Oct 10 2017

TNS-12547: TNS:lost contact and Linux Error: 32: Broken pipe While Connect Oracle 12c Data Guard

Category: Errors and SolutionsFatih Acar @ 15:39

I installed Oracle 12c Data Guard, everything was okey. But, I got an error like below when I wanted to connect data guard database with Toad or SQL Developer. tnsping was okey.

Error

SQL Developer :

Status : Failure -Test failed: IO Error: Got minus one from a read call

Listener.log

10-OCT-2017 15:01:16 * (CONNECT_DATA=(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=testuser))(SERVICE_NAME=TDG)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=testuser))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.24)(PORT=49834)) * establish * TDG * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe

Solution

If you have grid user to manage listener, you have to give permission on $ORACLE_HOME/bin/oracle executable file. You have to give chmod 6751 permission. This provides that grid user can execute oracle file like oracle user. Oracle open process on OS when you connect to database. If you connect from remote with using listener, listener owner (grid) will want to open new process on OS but grid user cannot open process with using $ORACLE_HOME/bin/oracle file due to do not have permission. So you have to give 6751 permission to use $ORACLE_HOME/bin/oracle file.

[root@tdg bin] chmod 6751 oracle

You can see the same problem at 11g and 12c version of Oracle Data Guard. When oracle rac installation or single instance installation with grid, oracle automatically change permission of $ORACLE_HOME/bin/oracle file to 6751.

1,950 total views, 50 views today

Tags: Oracle 12c, Oracle Administration, Oracle Error Solutions


Dec 08 2016

Update Another Column On The Same Table With Trigger While Updating Table in Oracle

Category: Errors and Solutions,Procedure,SQLFatih Acar @ 11:01

If you want to do update another column when execute an update on table, you can use before update trigger. If you use after update trigger, you can get some errors. if you use pragma autonomous_transaction parameter as declare in trigger you can get errors like that are “ORA-00060: deadlock detected while waiting for resource”, “ORA-06512: at “trigger_name” line 7″,”ORA-04088:error during execution of trigger “trigger_name”” if you don’t use, you can get errors like that are “ORA-04091: table tablename is mutating, trigger/function may not see it”,”ORA-06512: at trigger_name”, line 6″,”ORA-04088: error during execution of trigger trigger_name”

You can resolve this problem with using before update trigger. You can update another column of updating the same table.

Trigger Example

My example is about that If status column of table1 change from 0 to 1, I will write another columns that are name, surname and studentno at the same table table1.

create table table1 (id number, status number, name varchar2(100), surname varchar2(100), studentno varchar2(100),address varchar2(500),city varchar2(100));

create or replace trigger trg_trigger_name_upt
before update of status on table1
referencing OLD as old NEW as new
for each row
v_studentno varchar2(100);
begin

IF :old.status = 0 AND :new.status = 1 and UPDATING THEN

:new.name := ‘Fatih’;
:new.surname := ‘Acar’;
v_studentno := seq_studentno.nextval;
:new.studentno := v_studentno;

END IF;

end;
/

5,041 total views, no views today

Tags: Oracle Administration, Oracle Error Solutions, Oracle SQL Query


Oct 12 2016

Oracle 11g Oracle Checkpoint Not Complete, Cannot Allocate New Log Warning

Category: Administration,Errors and SolutionsFatih Acar @ 11:54

Error: Checkpoint not complete, Cannot Allocate New Log Warning

Thread 1 cannot allocate new log, sequence 125487
Checkpoint not complete

Solution: Checkpoint not complete messages are generated due to the logs are switching so fast that the checkpoint associated with the log switch isn’t complete. You should increase redo log file size and amount to resolve. Also, If you use archive_lag_target parameter as near zero for example like 1 or 2 minutes, you should change this parameter zero (no lag) or more than 10-15 min. Oracle recommends that redo log switch operation interval should be between 15-30 minutes.

oracle_redo_log_files

Show and change archive_lag_target parameter

SQL> show parameter archive_lag_target;
SQL> alter system set archive_lag_target=0 scope=both;
or
SQL> alter system set archive_lag_target=1800 scope=both;
1800 is 15 minutes. Parameter value as second.

If your archive_lag_target parameter is normal and you get checkpoint not complete error, you have to look your redo log file size and amount.
Continue reading “Oracle 11g Oracle Checkpoint Not Complete, Cannot Allocate New Log Warning”

5,759 total views, 12 views today

Tags: Oracle Error Solutions


Sep 24 2016

Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19700 to ORA-20000

Category: Errors and SolutionsFatih Acar @ 21:43

Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19700 to ORA-20000

ORA-19700: device type exceeds maximum length of string

Cause: The device type indicated is invalid.

Action: Supply a correct device type and retry the allocation.

ORA-19701: device name exceeds maximum length of string

Cause: The device name indicated is invalid.

Action: Supply a correct device name and retry the allocation.

ORA-19702: device parameter exceeds maximum length of string
ORA-19703: device command string exceeds maximum length of string

Cause: The device command string exceeds maximum length.

Action: Correct the command and retry the operation.

ORA-19704: file name exceeds maximum length of string

Cause: The specified file name, which was a parameter to a copy, backup, or restore operation, exceeds the maximum file name length for this operating system.

Action: Retry the operation with a shorter file name.

ORA-19705: tag value exceeds maximum length of string characters

Cause: During a backup or copy operation, the user supplied a tag value too long to fit in the file header.

Action: Supply a shorter tag and retry the operation.

ORA-19706: invalid SCN

Cause: The input SCN is either not a positive integer or too large.

Action: Check the input SCN and make sure it is a valid SCN.
Continue reading “Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19700 to ORA-20000”

5,770 total views, 10 views today

Tags: Oracle Error Solutions


Sep 24 2016

Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19500 to ORA-19700

Category: Errors and SolutionsFatih Acar @ 21:42

Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19500 to ORA-19700

ORA-19500: device block size string is invalid

Cause: the device block size returned by sequential I/O OSD is invalid

Action: If the block size was set by using the PARMS option of the Recovery Manager ALLOCATE CHANNEL command, then the specified block size must be changed. If no PARMS option was specified on the ALLOCATE CHANNEL command, then this is an internal error that should be reported to Oracle.

ORA-19501: read error on file “string“, block number string (block size=string)

Cause: read error on input file

Action: check the file

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

Cause: write error on output file

Action: check the file

ORA-19503: cannot obtain information on device, name=”string“, type=”string“, parms=”string

Cause: call to get device information returned an error

Action: check device name, type and parameters

ORA-19504: failed to create file “string

Cause: call to create file returned an error

Action: check additional messages, check access permissions.

ORA-19505: failed to identify file “string

Cause: call to identify the file returned an error

Action: check additional messages, and check if the file exists.
Continue reading “Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19500 to ORA-19700”

2,090 total views, 10 views today

Tags: Oracle Error Solutions


Sep 23 2016

Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19200 to ORA-19400

Category: Errors and SolutionsFatih Acar @ 21:37

Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19200 to ORA-19400

ORA-19200: Invalid column specification

Cause: All input arguments must be valid columns

Action: Specify a valid list of columns

ORA-19201: Datatype not supported

Cause: The particular datatype is not supported in the XMLGEN operator

Action: Specify only supported datatypes as arguments to the XMLGEN operator

ORA-19202: Error occurred in XML processingstring

Cause: An error occurred when processing the XML function

Action: Check the given error message and fix the appropriate problem

ORA-19203: Error occurred in DBMS_XMLGEN processingstring

Cause: An error occurred when processing the DBMS_XMLGEN functions

Action: Check the given error message and fix the appropriate problem

ORA-19204: Non-scalar value ‘string‘ is marked as XML attribute

Cause: Only scalar values (i.e. values which are not of object or collection type) can be marked as XML attribute,i.e. is prefixed by ‘@’. In this case, a non-scalar value was prefixed with ‘@’

Action: Remove the ‘@’ sign, or make the value a scalar.

ORA-19205: Attribute ‘string‘ qualifies a non-scalar value in the select list

Cause: The attribute immediately follows a value of object or collection type in the select list or type definition.

Action: Remove the ‘@’ sign, or make the previous value a scalar.
Continue reading “Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19200 to ORA-19400”

1,705 total views, 5 views today

Tags: Oracle Error Solutions


Sep 23 2016

Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19000 to ORA-19200

Category: Errors and SolutionsFatih Acar @ 21:35

Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19000 to ORA-19200

ORA-19000: missing RELATIONAL keyword

Cause: The keyword RELATIONAL in the work OBJECT RELATIONAL is missing in the XMLTYPE storage clause specification.

Action: Supply the RELATIONAL keyword in the storage clause

ORA-19001: Invalid storage option specified

Cause: An invalid storage option was specified for the XMLType.

Action: Supply a valid storage option.

ORA-19002: Missing XMLSchema URL

Cause: A XML schema URL must be specified in the storage option.

Action: Specify a URL for the XMLSchema.

ORA-19003: Missing XML root element name

Cause: A root element in the XMLSchema must be specified if the XML schema is given.

Action: Specify a root element in the XML schema.

ORA-19004: Duplicate XMLType OBJECT RELATIONAL storage option

Cause: A duplicate storage option for the XMLType column was specified

Action: Specify a single storage option for an XMLType column
Continue reading “Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19000 to ORA-19200”

2,645 total views, no views today

Tags: Oracle Error Solutions


Sep 22 2016

Oracle 12c R2 Error Codes and Solution Suggestions from ORA-18200 to ORA-19000

Category: Errors and SolutionsFatih Acar @ 21:32

Oracle 12c R2 Error Codes and Solution Suggestions from ORA-18200 to ORA-19000

ORA-18201: The input MDX string is empty.

Cause: The input MDX (MultiDimensional eXpression) string was empty.

Action: Correct the input MDX (MultiDimensional eXpression) string.

ORA-18202: MDX parser error at line string, column stringstring.

Cause: The input MDX (MultiDimensional eXpression) query had invalid syntax.

Action: Correct the syntax error for the MDX (MultiDimensional eXpression) query.

ORA-18203: The MDX string string is not currently supported.

Cause: The input MDX (MultiDimensional eXpression) query contained an unsupported feature.

Action: Modify the MDX (MultiDimensional eXpression) query to use only supported features.

ORA-18204: The identifier at line string, column string has no closing right bracket (]) character.

Cause: The delimited identifier had no closing right bracket (]) character.

Action: Correct the syntax error for the MDX (MultiDimensional eXpression) query.

ORA-18205: Invalid arguments for function string.

Cause: The function had invalid arguments.

Action: Correct the syntax error for the MDX (MultiDimensional eXpression) query.

ORA-18220: The type of the expression in string is stringstring is expected.

Cause: The MDX (MultiDimensional eXpression) query had the wrong type.

Action: Use an expression with a valid type.
Continue reading “Oracle 12c R2 Error Codes and Solution Suggestions from ORA-18200 to ORA-19000”

1,830 total views, no views today

Tags: Oracle Error Solutions


Sep 22 2016

Oracle 12c R2 Error Codes and Solution Suggestions from ORA-18000 to ORA-18200

Category: Errors and SolutionsFatih Acar @ 21:27

Oracle 12c R2 Error Codes and Solution Suggestions from ORA-18000 to ORA-18200

ORA-18000: invalid outline name

Cause: The parser detected a missing or invalid outline name

Action: n/a

ORA-18001: no options specified for ALTER OUTLINE

Cause: The parser detected that no clause was specified on the command Our performance tests, which are not typical as they exercise all branches of the code, have shown approximately a 30% performance increase line for ALTER OUTLINE.

Action: Re-issue the command, specifying a valid ALTER OUTLINE clause.

ORA-18002: the specified outline does not exist

Cause: Either the outline did not exist to begin with, or a timing window allowed for another thread to drop or alter the outline midstream.

Action: n/a

ORA-18003: an outline already exists with this signature

Cause: The signature generation algorithm generates signatures that are are 16 bytes in length so it is highly unlikely that any 2 signatures will be identical. This message is raised in such a rare case.

Action: Either re-issue the statement that led to the outline being created with some whitespace added or force the outline to be created in a different category.

ORA-18004: outline already exists

Cause: An outline already exists, either with the specified name, or for the specified SQL text.

Action: n/a
Continue reading “Oracle 12c R2 Error Codes and Solution Suggestions from ORA-18000 to ORA-18200”

1,920 total views, 5 views today

Tags: Oracle Error Solutions


Sep 22 2016

Oracle 12c R2 Error Codes and Solution Suggestions from ORA-17500 to ORA-18000

Category: Errors and SolutionsFatih Acar @ 21:24

Oracle 12c R2 Error Codes and Solution Suggestions from ORA-17500 to ORA-18000

ORA-17500: ODM err:string

Cause: An error returned by ODM library

Action: Look at error message and take appropriate action or contact Oracle Support Services for further assistance

ORA-17501: logical block size string is invalid

Cause: logical block size for oracle files must be a multiple of the physical block size, and less than the maximum

Action: check INIT.ORA file parameters

ORA-17502: ksfdcre:string Failed to create file string

Cause: file creation failed due to either insufficient OS permission or the file already exists

Action: check additional error messages

ORA-17503: ksfdopn:string Failed to open file string

Cause: file open failed due to either insufficient OS permission or the name of file exceeds maximum file name length.

Action: check additional error messages

ORA-17504: ksfddel:Failed to delete file string

Cause: The file that was being deleted is still in use or the process has insufficient permission to delete file.

Action: check additional error messages

ORA-17505: ksfdrsz:string Failed to resize file to size string blocks

Cause: There is insufficient space left on the device or the process has insufficient permission to resize file.

Action: check additional error messages
Continue reading “Oracle 12c R2 Error Codes and Solution Suggestions from ORA-17500 to ORA-18000”

6,270 total views, 30 views today

Tags: Oracle Error Solutions


Next Page »