Difference between revisions of "Oracle errors"
From dbawiki
| Line 10: | Line 10: | ||
</pre> | </pre> | ||
If this works, it is most probably a permissions error.<br /> | If this works, it is most probably a permissions error.<br /> | ||
| − | Re-run the <pre>$ORACLE_HOME/root.sh</pre> script that was run as part of the original installation. This will reset the permissions on some important files. | + | Re-run the |
| + | <pre> | ||
| + | $ORACLE_HOME/root.sh | ||
| + | </pre> | ||
| + | script that was run as part of the original installation. This will reset the permissions on some important files. | ||
| + | ===Database trigger to capture ORA errors=== | ||
| + | From [http://ora-ssn.blogspot.be/2011/10/trigger-on-database-to-capture-ora.html ora-ssn.blogspot.be] | ||
| + | <pre> | ||
| + | CREATE TABLE stats$error_log ( | ||
| + | err_dt TIMESTAMP, | ||
| + | db_user VARCHAR2(30), | ||
| + | msg_stack VARCHAR2(2000), | ||
| + | sqltxt VARCHAR2(1000)) | ||
| + | tablespace users; | ||
| + | </pre> | ||
| + | Now, create a trigger on the database server error. | ||
| + | <pre> | ||
| + | CREATE OR REPLACE TRIGGER log_server_errors | ||
| + | AFTER SERVERERROR | ||
| + | ON DATABASE | ||
| + | DECLARE | ||
| + | v_sqltext VARCHAR2(1000); | ||
| + | nl_sqltext ora_name_list_t; | ||
| + | BEGIN | ||
| + | -- Capture entire error text | ||
| + | FOR i IN 1 .. ora_sql_txt(nl_sqltext) LOOP | ||
| + | v_sqltext := v_sqltext || nl_sqltext(i); | ||
| + | END LOOP; | ||
| + | INSERT INTO STATS$ERROR_LOG | ||
| + | (err_dt, db_user, msg_stack, sqltxt) | ||
| + | VALUES | ||
| + | (systimestamp, | ||
| + | sys.login_user, | ||
| + | dbms_utility.format_error_stack, v_sqltext); | ||
| + | END log_server_errors; | ||
| + | / | ||
| + | </pre> | ||
Revision as of 09:44, 28 January 2013
ORA-12547: TNS lost contact
If
sqlplus <user>/<pass>
fails with above error, try
sqlplus <user>/<pass>@<db connection>
If this works, it is most probably a permissions error.
Re-run the
$ORACLE_HOME/root.sh
script that was run as part of the original installation. This will reset the permissions on some important files.
Database trigger to capture ORA errors
From ora-ssn.blogspot.be
CREATE TABLE stats$error_log (
err_dt TIMESTAMP,
db_user VARCHAR2(30),
msg_stack VARCHAR2(2000),
sqltxt VARCHAR2(1000))
tablespace users;
Now, create a trigger on the database server error.
CREATE OR REPLACE TRIGGER log_server_errors
AFTER SERVERERROR
ON DATABASE
DECLARE
v_sqltext VARCHAR2(1000);
nl_sqltext ora_name_list_t;
BEGIN
-- Capture entire error text
FOR i IN 1 .. ora_sql_txt(nl_sqltext) LOOP
v_sqltext := v_sqltext || nl_sqltext(i);
END LOOP;
INSERT INTO STATS$ERROR_LOG
(err_dt, db_user, msg_stack, sqltxt)
VALUES
(systimestamp,
sys.login_user,
dbms_utility.format_error_stack, v_sqltext);
END log_server_errors;
/
