Need a lot of undo capabilities? You have to set this...

When you experience the "error ORA-01555: snapshot too old", i am sure you wish to have had larger undo provisioned space.
First:

sql>  show parameter undo_retention

undo_retention                       integer     300000

Keep in mind the displayed value is measured in seconds. The first thought will be to raise ad infinitum the value of the above parameter. But this is not posible, because you need a lot of space, and the database storage is not endless. Anyway, the database engine will not honour any request of keeping undo information beyond of its capabilities. Will erase the oldest undo info and will continue to keep in shape the data. 
So, you need to set properly the undo_retention parameter. Take the following script and use it to obtain the optimal value:


select (SELECT SUM(a.bytes)
 FROM v$datafile a,
 v$tablespace b,
 dba_tablespaces c
 WHERE c.contents = 'UNDO'
 AND c.status = 'ONLINE'
 AND b.name = c.tablespace_name
 AND a.ts# = b.ts#)
/
(
( SELECT TO_NUMBER(value)   FROM v$parameter  WHERE name = 'db_block_size') -
(SELECT MAX(undoblks/((end_time-begin_time)*3600*24)
)   FROM v$undostat)) 
"undo_retention optimal value" 
from dual;

How to workaround a hanging situation when run an Oracle APEX saved report

The problem seems to be impossible, at first sight. As a regular user you cannot do anything but wait.
Log on as developer or admin OF THE WORKSPACE WHERE THE REPORT BELONGS. Go to:

Home>>Administration>>Manage Service>>Interactive Report Settings (link on the right of the page) >>Saved Reports

 select the desired saved report and click <delete>. Unfortunately, there is no other way to stop the hung report. Of course, maybe it will need to rebuild the report, but, anyway, it was designed in a poorly fashion, since is not finishing the run and displaying some data.

Import Excel data in Oracle tables using TOAD

Creation of the table:

Database >> Create >> Table >> Load Cols from File >> choose your file of choice >>  OK >> OK

Filling the table:

Schema Browser >> Tables >> Right click the table to be filled >> Import from Text, Excel or Access >> Next >> Next >> choose the file >> Next >> set some or Next >> choose the sheet (if you want two or more sheets, will import one at a time >> Next >> Execute

Enjoy!

Solve "Enq: TM Contention" when unindexed foreign keys is the cause

If you notice a very slow behaviour of your database and, from the multiple ways you can go further,  you issue the following command

SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, l.request, o.object_name, o.object_type
FROM v$lock l, dba_objects o, v$session s
WHERE UPPER(s.username) = UPPER('&User')
AND l.id1 = o.object_id (+)
AND l.sid = s.sid
ORDER BY sid, type;

and you receive a line like this:

SID   BLOCK   EVENT                  TYPE  MODE  REQ  OBJECT     OBJECT_TYPE
83    54      enq: TM - contention   TM    0     2    STUDENTS   TABLE


you should take care of this problem, because will not disappear by itself.

First, locate the unindexed foreign keys:


SELECT * FROM (SELECT a.table_name, b.column_name, b.position column_position
FROM   user_constraints a, user_cons_columns b
WHERE  a.constraint_name = b.constraint_name
AND    a.constraint_type = 'R'
MINUS
SELECT c.table_name, d.column_name, d.column_position
FROM   user_indexes c, user_ind_columns d
WHERE  c.index_name = d.index_name
)
ORDER BY table_name, column_position;

and take the appropriate action, either drop some foreign key constraints if you don't need them  or create the proper indexes. 
Good luck!

How to overcome "ORA-01610: recovery using the BACKUP CONTROLFILE option must be done"

SQL> startup mount

ORACLE instance started.

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

This problem can appear after a loss of the controlfile (and will assume you don't have any backup) or if you mess with point-in-time recovery.

Solution:

SQL> recover database using BACKUP CONTROLFILE;

-this command will lead to some interaction about what file (controlfile backup) the instance should use in order to recover.

I will assume that you can't provide any file an the problem persists.

Now take a backup of the current controlfile:\

SQL> alter database backup controlfile to '/home/oracle/back.ctl';

Create a trace of the current controlfile:

SQL> alter database backup controlfile to trace as '/home/oracle/trace.sql';

Bounce the instance to the nomount state:

SQL>shutdown immediate;

SQL>startup nomount;

Now copy the following command from the trace file:

CREATE CONTROLFILE REUSE DATABASE "PIDB" NORESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 1168

LOGFILE

  GROUP 1 '/opt/oracle/oradata/pidb/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/opt/oracle/oradata/pidb/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/opt/oracle/oradata/pidb/redo03.log'  SIZE 50M BLOCKSIZE 512

DATAFILE

  '/opt/oracle/oradata/pidb/system01.dbf',

  '/opt/oracle/oradata/pidb/sysaux01.dbf',

  '/opt/oracle/oradata/pidb/undotbs01.dbf',

  '/opt/oracle/oradata/pidb/users01.dbf',

  '/opt/oracle/oradata/pidb/example01.dbf',

  '/opt/oracle/oradata/smis.dbf',

  '/opt/oracle/oradata/smis2.dbf',

  '/opt/oracle/oradata/smis3.dbf',

  '/opt/oracle/oradata/pidb/STATSPACK.DBF',

  '/opt/oracle/oradata/pidb/FLOW_1046809878404304.dbf',

  '/opt/oracle/oradata/pidb/apex01.dbf',

  '/opt/oracle/oradata/pidb/APEX_2146717548170215.dbf'

CHARACTER SET WE8MSWIN1252

;

and issue it through sqlplus command line.

You can easily observe that the TEMP datafiles are not the, and after:

SQL>alter database open;

you cannot do everything you want, because of the emptiness of TEMP tablespace.

So, create a temporary datafile:

SQL>alter tablespace TEMP add tempfile '/opt/oracle/oradata/pidb/tempo2.dbf' size 1024m autoextend on;

and everything will work fine.

Be aware, if the database is after an incomplete recovery, you should replace the NORESETLOGS word with RESETLOGS, within the create controlfile command, and the further command will be

SQL> alter database open resetlogs;

All done! Enjoy!

Some Glassfish issues: instalation, stuff.....

In order to have the Glassfish server working, you must install JDK 1.7xxxxx.

So, download from here the jdk kit.

Then, download from here the glassfish server. I preffer the linux version.

Now, let's assume that you have the jdk folder under /usr/java and glassfish4 folder under /opt.

Make an environment script:

vi envjava.sh

export JAVA_HOME=/usr/java/jdk1.7.0_60/bin
export PATH=$JAVA_HOME/bin:/opt/glassfish4/bin:$PATH

and run the script.

All you have to do now is to start the domain:

/opt/glassfish4/bin/asadmin start-domain

Let's study some problems that can appear:

• Exception in thread "main" java.lang.UnsupportedClassVersionError: org/glassfish/admin/cli/AsadminMain : Unsupported major.minor version 51.0

the problem is java executable is another version, lower one. Make a symbolic link:

 rm /usr/bin/java

 ln -s /usr/java/jdk1.7.0_60/bin/java /usr/bin/java

• java.net.UnknownHostException: ws1: ws1: Temporary failure in name resolution

your /etc/hosts file is wrong.

• There is a process already using the admin port 4848 -- it probably is another instance of a GlassFish server.

Could be the same problem as above, the file /etc/hosts should resolve the ip properly, or can be a problem with that port. The netstat will help:

netstat -natp

if you'll receive something like this:

tcp   0  0 :::4848       :::*            LISTEN      7943/java

just kill that process, if it is not a wanted process for you:

ps -ef | grep java
root      7943     1 15 11:12 pts/1    00:00:38 /usr/java/jdk1.7.0_60/bin/java  .....................

kill -9 7943

and that's all.

Now, you should see this:

/opt/glassfish4/bin/asadmin start-domain
Waiting for domain1 to start .......
Successfully started the domain : domain1
domain  Location: /opt/glassfish4/glassfish/domains/domain1
Log File: /opt/glassfish4/glassfish/domains/domain1/logs/server.log
Admin Port: 4848
Command start-domain executed successfully.

Open in a browser

http:<your hostname or ip>:4848

Now, you can experience the following error:

Secure Admin must be enabled to access the DAS remotely

First, you have to change the admin password, otherwise the secure mode cannot be enabled.

/opt/glassfish4/bin/asadmin change-admin-password

the old password is blank (or adminadmin, or admin, ...). As far as i know, is blank.

After the password is changed:

/opt/glassfish4/bin/asadmin

>enable-secure-admin

>exit


and now everything should be fine.
The admin console has to be there.

Now, let's create a jdbc connection pool:

copy the ojdbc6.jar from here and put it here:

/opt/glassfish4/glassfish/domains/domain1/lib/

then restart the domain:

/opt/glassfish4/bin/asadmin

>restart-domain

> create-jdbc-connection-pool --restype javax.sql.DataSource --datasourceclassname oracle.jdbc.pool.OracleDataSource --property "user=hr:password=hr: url=jdbc\\:oracle\\:thin\\:@ws1\\:1521\\:ws1" ws1pool

where we assume that hr/hr is the user credentials, ws1 is the hostname, 1521 the listener port and ws1 the service name.

Now go to the admin console, jdbc connection pools, click on the new connection pool name, and then ping it. You should receive the message "ping succeeded".

How to reset root password under LINUX

You will find this tutorial on hundreds of places over the web, but it's never too much for a useful thing to be found on how many places can be posible.

1. Start the computer.
2. Soon after the BIOS messages, pres ESC and you'll have the GRUB menu displayed.
3. Press <E> and in the following menu, use the arrows to choose the second line, usually, the one with kernel = ....
4. Press <E> again and begin to edit the line, by adding the word "single" at the end (keep in mind you can use the word "1". Just that, the "1" character).
5. ESC to go back
6. Press <B> to boot the kernel.

The booting process will end in single user mode, with root prompt (sh#).
Now you can change the password easily, like you would be logged as root.

sh# passwd

etc etc

Enjoy!