Oracle Database Discussions

KarthikSingh wrote:


Thu Mar  5 20:41:37 2015
alter tablespace temp ADD TEMPFILE '/ERP/oramasknew/maskdata1/temp01.dbf' size 500M autoextend on NEXT 64K
Thu Mar  5 20:41:37 2015
Completed: alter tablespace temp ADD TEMPFILE '/ERP/oramaskne
Fri Mar  6 08:42:18 2015
alter tablespace temp ADD TEMPFILE '/ERP/oramasknew/maskdata1/temp04.dbf' REUSE
Fri Mar  6 08:42:18 2015
Completed: alter tablespace temp ADD TEMPFILE '/ERP/oramaskne
Fri Mar  6 08:42:56 2015
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 9
Fri Mar  6 08:42:59 2015
ALTER DATABASE CLOSE NORMAL

Any kind of help would be much appreciated.

Thanks,
Karthik

From the alert log file we could see that the creation of 500M temp file took less than a second and even db shut down happened in few seconds, hence it appears there is no problem with the database. May be you are not getting the sql prompt back? Can you open a SQL session and try for some transactions?

Hi Michael,

Thanks for your response. I tried to open another 2 more sql sessions and issue the connect string

SQL> conn apps

Enter password:

It hangs after giving the password.

After Pressing ctrl+c the following output

SQL> conn apps/apps

;

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-01013: user requested cancel of current operation

Warning: You are no longer connected to ORACLE.

Thanks,

Karthik

Hi Karthik,

Set required Oracle environment variable and try to connect in both ways;

1)  as sysdba

2) using tnsnames connection string.

Before you try this make sure Listener is up and running and database service is registered with. Also test $tnsping  <connection string name>    before connection.

HTH,

Pradeep

Hi Pradeep,

Thanks for your response. Please see the below outputs

su - oramasknew

[oramasknew@oramask ~]$ cd /ERP/oramasknew/maskdb/9.2.0/

[oramasknew@oramask 9.2.0]$ ls

1.sql       assistants  demo         inventory  jsp               oci           ord      relnotes       sqlplus      ultrasearch

admin       BC4J        diagnostics  jar        ldap              ocommon       otrace   root.sh        stage_tmp    utl.lst

Apache      bin         dm           javavm     lib               ocs4j         oui      root.sh.old    stk.lst      weboamlib

appsjre118  classes     doc          jdbc       MASK_oramask.env  oem_webstage  owm      root.sh.old.1  syndication  wwg

appsmisc    ctx         ds           jlib       md                olap          plsql    slax           sysman       xdk

appsoui     cwmlite     hs           jre        mgw               OPatch        precomp  soap           temp

appsutil    dbs         install      JRE        network           oracore       rdbms    sqlj           test.sh

[oramasknew@oramask 9.2.0]$ . MASK_oramask.env

[oramasknew@oramask 9.2.0]$ lsnrctl status MASK

LSNRCTL for Linux: Version 9.2.0.6.0 - Production on 06-MAR-2015 12:35:02

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCMASK))

STATUS of the LISTENER

------------------------

Alias                     MASK

Version                   TNSLSNR for Linux: Version 9.2.0.6.0 - Production

Start Date                06-MAR-2015 10:12:23

Uptime                    0 days 2 hr. 22 min. 39 sec

Trace Level               off

Security                  OFF

SNMP                      OFF

Listener Parameter File   /ERP/oramasknew/maskdb/9.2.0/network/admin/MASK_oramask/listener.ora

Listener Log File         /ERP/oramasknew/maskdb/9.2.0/network/admin/mask.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCMASK)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oramask.test.com)(PORT=1531)))

Services Summary...

Service "MASK" has 1 instance(s).

  Instance "MASK", status UNKNOWN, has 1 handler(s) for this service...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oramasknew@oramask 9.2.0]$ ps -ef|grep smon

523       9946     1  0 08:55 ?        00:00:00 ora_smon_MASK

523      24687 24333  0 12:35 pts/4    00:00:00 grep smon

You have new mail in /var/spool/mail/oramasknew

[oramasknew@oramask 9.2.0]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Mar 6 12:35:45 2015

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL> quit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

[oramasknew@oramask 9.2.0]$ tnsping MASK

TNS Ping Utility for Linux: Version 9.2.0.6.0 - Production on 06-MAR-2015 12:36:03

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:

/ERP/oramasknew/maskdb/9.2.0/network/admin/MASK_oramask/sqlnet_ifile.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=ORAMASK.test.com)(PORT=1531)) (CONNECT_DATA= (SID=MASK)))

OK (0 msec)

Thanks,

Karthik

See if you have any logon triggers. In similar lines https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:4018285967344

If I understood, you local connection is working fine, right ?

Did you test sqlplus using connection string e.g. sqlplus  user/pwd@MASK  ?   and what was the result ?

No only sys user is logging correctly.

sqlplus /nolog

conn / as sysdba; --> Works fine

sqlplus apps/******@MASK --> Hangs

Did you check connecting to the database using another other schema ?

sqlplus <other schema name>/******@MASK

Yes. As I told only sys is working fine.

I just checked with scott and it is still hanging..

Fri Mar  6 09:00:54 2015

SMON: enabling tx recovery

Fri Mar  6 09:00:54 2015

Database Characterset is US7ASCII

replication_dependency_tracking turned off (no async multimaster replication found)

Completed: alter database open noresetlogs

Fri Mar  6 09:01:13 2015

Restarting dead background process QMN0

QMN0 started with pid=13

Fri Mar  6 09:12:50 2015

ORACLE Instance MASK - Can not allocate log, archival required

Fri Mar  6 09:12:50 2015

ARCH: Connecting to console port...

Thread 1 cannot allocate new log, sequence 4

All online logs needed archiving

  Current log# 1 seq# 3 mem# 0: /ERP/oramasknew/maskdata1/log01a.dbf

  Current log# 1 seq# 3 mem# 1: /ERP/oramasknew/maskdata1/log01b.dbf

Fri Mar  6 10:17:56 2015

Shutting down instance: further logons disabled

Shutting down instance (immediate)

License high water mark = 6

Fri Mar  6 10:22:57 2015

Active call for process 10225 user 'oramasknew' program 'oracle@oramask.test.com (J000)'

SHUTDOWN: waiting for active calls to complete.

May be try bouncing the OS box and see if it helps..

Okay let me give a restart and let you know.

Before that I tried to give a shutdown immediate and last few lines of alert log

SHUTDOWN: waiting for active calls to complete.

Fri Mar  6 13:54:19 2015

Shutting down instance: further logons disabled

Shutting down instance (immediate)

License high water mark = 6

Then I gave a kill -9 for the process.

Fri Mar  6 13:55:01 2015

Errors in file /ERP/oramasknew/maskdb/9.2.0/admin/MASK_oramask/bdump/mask_pmon_9938.trc:

ORA-00474: SMON process terminated with error

Fri Mar  6 13:55:01 2015

PMON: terminating instance due to error 474

Instance terminated by PMON, pid = 9938

Additionally I was able to get this link addressing the issue "PMON: terminating instance due to error 474"

Hello,

Can you check this command connecting as a sysdba;

alter system switch logfile; 

?

Hi Michael,

michaelrozar17 wrote:

May be try bouncing the OS box and see if it helps..

After giving a O/s Level Restart now everything is working like charm. Its awesome. How is that, I couldn't understand still what could be the reason.

I'm facing a different issue in starting the apps tier, let me open another thread in Right location.

Thanks,

Karthik

Hi 12cdb,

Do you want me to still provide the alter switch log file ?

Thanks,

Karthik

After giving a O/s Level Restart now everything is working like charm. Its awesome. How is that, I couldn't understand still what could be the reason.

Unless I missed something, you did not specify your OS version : post the result of

$ cat /etc/issue

There is nothing wrong in trying 2-3 times.

alter system switch logfile;

at the same time keep checking the alert log for log switch message.

hth