Configure Archive Log in RAC

The post has been moved to another location. Click Here to access the post.

Start and Stop Instances in RAC

You can start and stop database and instances in RAC environment using Server Control (SRVCTL) utility.
Always use the SRVCTL utility from ORACLE_HOME location
$ cd $ORACLE_HOME

To start a database

$ srvctl start database -d database_name

To stop a database

$ srvctl stop database -d database_name

To start an instance

$ srvctl start instance -d database_name -i instance_name

To stop an instance

$ srvctl stop instance -d database_name -i instance_name

Exp-00091 Exporting questionable statistics.

Set the statistics=none to avoid this error.
The default value is Estimate.

The error is caused by one of the following

1. Your table rows have fatel error while exporting.
2. You have used query parameter
3. client and server characterset mismatch.

After importing the data you have to gather fresh statistics to use it. So it is always advisable to use statistics=none.

Will DDL statement commit automatically

I have read in some books that issuing ddl statement will commit the transaction automatically irrespective of the success of the DDL statement.

I performed a small test and came to a conclusion that,

• If ddl statement is executed successfully, the transaction is committed automatically.
• If ddl statement fails due to logical error, the transaction is committed automatically.
• If ddl statement fails due to syntax error, the transaction is neither committed nor rolled back, the transaction is still open.

Details of the test performed

SQL> create table testddl ( a number, b date);
Table created.
SQL> insert into testddl values (1, sysdate);
1 row created.
SQL> insert into testddl values (2, sysdate);
1 row created.
Not committed.
SQL> alter table testddl add constraint a_uq unique (a);
Table altered.
Automatically commits - ddl statement succeed.

SQL> rollback;
Rollback complete.

SQL> select * from testddl;
A B
-- --------
1 10-SEP-08
2 10-SEP-08

SQL> insert into testddl values (3, sysdate);
1 row created.
SQL> insert into testddl values (4, sysdate);
1 row created.
not committed

SQL> alter table testddl add constraint b_fk b references test (b);
alter table testddl add constraint b_fk b references test (b)
*
ERROR at line 1:ORA-01430: column being added already exists in table

Error but automatically commits. DDL Failed due to logical error.

SQL> rollback;
Rollback complete.

SQL> select * from testddl;
A B
-- --------
1 10-SEP-08
2 10-SEP-08
3 10-SEP-08
4 10-SEP-08

SQL> insert into testddl values (5, sysdate);
1 row created.
SQL> insert into testddl values (6, sysdate);
1 row created.
not committed

SQL> alter table testddl drp constraint a_uq;
alter table testddl drp constraint a_uq
*
ERROR at line 1:ORA-01735: invalid ALTER TABLE option

Error. ddl failed due to syntax error.Will not commit automatically.

SQL> rollback;
Rollback complete.

SQL> select * from testddl;
A B
-- --------
1 10-SEP-08
2 10-SEP-08
3 10-SEP-08
4 10-SEP-08

This is how the ddl statement is processed.
DDL parse
If success
commit
execute ddl
commit
end if
else
do nothing.

Hope you have enjoyed...

dblinks...compile problem in forms6i

I faced a bizarre problem while using database link in forms6i. I created a public database link. When I queried through sql*plus I was able to retrieve the data. That means everything was fine with tnsnames.ora and the network. But when I issued the same statement in the forms6i, i was not able to compile it.When i tried to compile (Ctl + k) and (Ctl + Shift +K) the form builder was closed without throwing any error message. I created synonym for the database link, but still the problem persists. Our database is in 9i and the remote database is in 10g.I thought it could be due to the version problem. I created a view for that statement, and finally i was able to compile it.

So if any of you are facing such problem, one solution is to create a view for the statement that uses database link.If any of you had faced such error and got a solution please do share with me.

ORA-03113 End of File on Communication Channel

Last week we had a problem with the annoying error message - ORA-03113 end of file on communication channel. Usually the users will run reports that will take 3 to 4 hours. Since last week they were not able to run the reports, it terminated with the error ORA-03113.

There was no error reported in the alert log file and no trace file generated. I even tried to alter the value of SQLNET.ORA parameter sqlnet.expire_time, but nothing worked.

The network was fine and there is no DBLINK in the database.

After much diagnosis and discussion with the networking people, i found the error was due to the timeout connection setting in the firewall. We changed the value from 1 hr to 5 hr and now we are not facing the error.

How to solve ORA-03113 Error

It occurs because the connection between client and server process was broken. We need to find out why the connection got broken.There are many reasons for that. Use the following steps to find out the possible reason.

Step 1: Look for the Alert log file for any error message. If the ORA-03113 error is reported in the alert log it will be accompanied by any othererror message. For example Oracle internal error, or server down etc etc etc.. try to solve that issue. If there is no error in the alert log or no trace file generated then it may not be due to a database error.

Step 2: Look for listener.log file size. If the file size is too large, try to stop the listener, create a new listener.log and restart the listener.

$ LSNRCTL STOP
$ mv listener.log listener_old.log
$ LSNRCTL START ( This will create a new listener.log file).

Step 3: Check for network issues. Ping the server. Check for any protocol error. Get the help of networking people in resolving these errors.

Step 4: Firewall error. There is a setting in the firewall namely, connection timeout, look for the value in this setting. Change to higher valueand check. One way to find whether the error is due to firewall setting is open a telnet session simultaneously and leave it idle till your report runs or returns error. Then try to issue any command in the telnet if the session hangs and you are not able to type the command then the error is due to the firewall setting.

Step 5: If none of the above step works. Contact Oracle Support.