Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This morning i had a weird problem just opening the SQL developer i cannot connect to my database. I checked the log file of the impdp operation launched before (14G), all the data is imported without errors. But I cannot access to the database, I tried with the SQL Developer or with SQL*Plus.

I obtained the following error message: error message window

I think the problem came with the log or archiving. I think i did a little mistake creating the oracle's instance. After having beed seen this error message i've seen that the oraarch folder is full of big sized files. I deleted them but this didn't solved the connection problem.

I think the problem is in the Ora parameters' file. Creating the instance in archivelog mode

 shutdown immediate
    startup mount
    alter database archivelog;
    alter database open;
    spool off

and archivelog folder and name files definition :

echo "log_archive_dest_1='LOCATION=$ORADATA_BASE/$SID/oraarch'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "log_archive_format='${SID}_%r_%s_%t.arc'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora

Here the file creating when creating the instance:

echo "db_domain=''" > $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "db_name='$SID'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "instance_name='$SID'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "db_block_size=8192" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "aq_tm_processes=0" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "optimizer_mode=CHOOSE" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "O7_dictionary_accessibility=FALSE" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "Remote_os_roles=FALSE" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "timed_statistics=TRUE" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "Workarea_size_policy='AUTO'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "undo_management='AUTO'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "undo_tablespace='UNDO'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "undo_retention=1800" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "java_pool_size=0" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "filesystemio_options=setall" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "remote_login_passwordfile=EXCLUSIVE" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "audit_file_dest='$STRUCTURE_ADMIN/$SID/audit'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "background_dump_dest='$STRUCTURE_ADMIN/$SID/bdump'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "core_dump_dest='$STRUCTURE_ADMIN/$SID/cdump'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "user_dump_dest='$STRUCTURE_ADMIN/$SID/udump'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "Remote_os_authent=FALSE" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "optimizer_index_caching=90" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "optimizer_index_cost_adj=10" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "db_file_multiblock_read_count=16" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "control_files='$ORADATA_BASE/$SID/oractl01/${SID}_ctrl01.ctl','$ORADATA_BASE/$SID/oractl02/${SID}_ctrl02.ctl','$ORADATA_BASE/$SID/oradata01/${SID}_ctrl03.ctl'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "log_archive_dest_1='LOCATION=$ORADATA_BASE/$SID/oraarch'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "log_archive_format='${SID}_%r_%s_%t.arc'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "pga_aggregate_target=256M" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "processes=151" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "sga_target=2G" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "sessions=151" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
echo "Connect par SQLPLUS*"
share|improve this question

1 Answer 1

Basically your database has stalled because it has ran out of space to store archive logs, and needs to switch one.

To remedy, delete the generated logs (assuming you don't need them for backup and recovery purposes), then execute (as SYSDBA):

alter system archive log all;

alter system switch logfile;

If you don't need to run your database in archivelog mode (ie: you don't need to be able to do any disaster recovery & don't care if your data goes AWOL), you can turn archivelog mode off (as SYSDBA, database in MOUNT mode):

alter database noarchivelog;

alter database open;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.