Oracle

Startup Fails With ORA-01012: Not Logged On

Problem Description
Users cant able to login to database server. Though it allow sysdba to login, it does not allow to run any query. Sometimes it will show database is connected to an idle instance. But when we startup database it shall throw error ORA-01081: cannot start already-running .

[oracle@mezon01 ~]$ sqlplus sys/System_2018@IIRDB

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 24 08:03:07 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server

 

Enter user-name: sys
Enter password:
ERROR:
ORA-00020: maximum number of processes (1500) exceeded

 

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

 

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@mezon01 ~]$ sqlplus -prelim “/as sysdba”

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 24 08:04:06 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL> show parameter sga_t;
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

 

SQL> startup;
ORA-01012: not logged on
SQL> shutdown immediate;
ORA-01012: not logged on
SQL> show parameter sga_max;
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

SQL> EXİT
SP2-0042: unknown command “EXİT” – rest of line ignored.
SQL> exit
Disconnected from ORACLE
[oracle@mezon01 ~]$ ps -ef | grep IIRDB
oracle 6096 1 0 08:05 ? 00:00:00 ora_pmon_IIRDB
oracle 6098 1 0 08:05 ? 00:00:00 ora_psp0_IIRDB
oracle 6136 1 1 08:05 ? 00:00:01 ora_vktm_IIRDB
oracle 6140 1 0 08:05 ? 00:00:00 ora_gen0_IIRDB
oracle 6142 1 0 08:05 ? 00:00:00 ora_diag_IIRDB
oracle 6144 1 0 08:05 ? 00:00:00 ora_dbrm_IIRDB
oracle 6146 1 0 08:05 ? 00:00:00 ora_ping_IIRDB
oracle 6148 1 0 08:05 ? 00:00:00 ora_acms_IIRDB
oracle 6150 1 0 08:05 ? 00:00:00 ora_dia0_IIRDB
oracle 6152 1 0 08:05 ? 00:00:00 ora_lmon_IIRDB
oracle 6162 1 0 08:05 ? 00:00:00 ora_lmd0_IIRDB
oracle 6164 1 0 08:05 ? 00:00:00 ora_rms0_IIRDB
oracle 6166 1 0 08:05 ? 00:00:00 ora_lmhb_IIRDB
oracle 6172 1 4 08:05 ? 00:00:04 ora_mman_IIRDB
oracle 6174 1 0 08:05 ? 00:00:00 ora_dbw0_IIRDB
oracle 6176 1 0 08:05 ? 00:00:00 ora_dbw1_IIRDB
oracle 6178 1 0 08:05 ? 00:00:00 ora_dbw2_IIRDB
oracle 6180 1 0 08:05 ? 00:00:00 ora_dbw3_IIRDB
oracle 6182 1 0 08:05 ? 00:00:00 ora_lgwr_IIRDB
oracle 6184 1 0 08:05 ? 00:00:00 ora_ckpt_IIRDB
oracle 6186 1 0 08:05 ? 00:00:00 ora_smon_IIRDB
oracle 6188 1 0 08:05 ? 00:00:00 ora_reco_IIRDB
oracle 6190 1 0 08:05 ? 00:00:00 ora_rbal_IIRDB
oracle 6192 1 0 08:05 ? 00:00:00 ora_asmb_IIRDB
oracle 6194 1 0 08:05 ? 00:00:00 ora_mmon_IIRDB
oracle 6198 1 0 08:05 ? 00:00:00 ora_mmnl_IIRDB
oracle 6200 1 0 08:05 ? 00:00:00 ora_d000_IIRDB
oracle 6202 1 0 08:05 ? 00:00:00 ora_s000_IIRDB
oracle 6204 1 0 08:05 ? 00:00:00 ora_mark_IIRDB
oracle 6206 1 0 08:05 ? 00:00:00 ora_ocf0_IIRDB
oracle 6325 1 0 08:05 ? 00:00:00 ora_o000_IIRDB
oracle 10089 28710 0 08:07 pts/1 00:00:00 grep IIRDB
oracle 20902 25965 0 May23 ? 00:04:20 /oracle/product/11.2.0.4/db/jdk/bin/java -server -Xmx384M -XX:MaxPermSize=400M -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=40 -DORACLE_HOME=/oracle/product/11.2.0.4/db -Doracle.home=/oracle/product/11.2.0.4/db/oc4j -Doracle.oc4j.localhome=/oracle/product/11.2.0.4/db/mezon01_IIRDB/sysman -DEMSTATE=/oracle/product/11.2.0.4/db/mezon01_IIRDB -Doracle.j2ee.dont.use.memory.archive=true -Djava.protocol.handler.pkgs=HTTPClient -Doracle.security.jazn.config=/oracle/product/11.2.0.4/db/oc4j/j2ee/OC4J_DBConsole_mezon01_IIRDB/config/jazn.xml -Djava.security.policy=/oracle/product/11.2.0.4/db/oc4j/j2ee/OC4J_DBConsole_mezon01_IIRDB/config/java2.policy -Djavax.net.ssl.KeyStore=/oracle/product/11.2.0.4/db/sysman/config/OCMTrustedCerts.txt-Djava.security.properties=/oracle/product/11.2.0.4/db/oc4j/j2ee/home/config/jazn.security.props -DEMDROOT=/oracle/product/11.2.0.4/db/mezon01_IIRDB -Dsysman.md5password=true -Drepapi.oracle.home=/oracle/product/11.2.0.4/db -Ddisable.checkForUpdate=true -Doracle.sysman.ccr.ocmSDK.websvc.keystore=/oracle/product/11.2.0.4/db/jlib/emocmclnt.ks -Dice.pilots.html4.ignoreNonGenericFonts=true -Djava.awt.headless=true -jar /oracle/product/11.2.0.4/db/oc4j/j2ee/home/oc4j.jar -config /oracle/product/11.2.0.4/db/oc4j/j2ee/OC4J_DBConsole_mezon01_IIRDB/config/server.xml
oracle 25965 1 0 May23 ? 00:00:12 /oracle/product/11.2.0.4/db/perl/bin/perl /oracle/product/11.2.0.4/db/bin/emwd.pl dbconsole /oracle/product/11.2.0.4/db/mezon01_IIRDB/sysman/log/emdb.nohup

 

————————————————————————————————————————————-

[oracle@mezon01 ~]$ sysresv
IPC Resources for ORACLE_SID “IIRDB” :
Shared Memory:
ID KEY
100270098 0x00000000
100302867 0x00000000
100335643 0x14203f64
Semaphores:
ID KEY
136445992 0xf1e96e54
136478764 0xf1e96e55
136511533 0xf1e96e56
136544302 0xf1e96e57
136577071 0xf1e96e58
136609840 0xf1e96e59
136642609 0xf1e96e5a
136675378 0xf1e96e5b
136708147 0xf1e96e5c
Oracle Instance alive for sid “IIRDB”
[oracle@mezon01 ~]$ ipcrm -m 100270098
[oracle@mezon01 ~]$ ipcrm -m 100302867
[oracle@mezon01 ~]$ ipcrm -m 100335643
[oracle@mezon01 ~]$
[oracle@mezon01 ~]$
Changes
Oracle has been forcefully shutdown at OS level or crashed.

CAUSE
An orphaned shared memory segment belonging to the ORACLE_SID still exists from a previous instance startup.

The command
ps -ef | grep $ORACLE_SID

shows no processes but with ORACLE_SID set the Oracle ‘sysresv’ utility shows a shared memory segment for a non-existing instance, e.g.

$ sysresv

IPC Resources for ORACLE_SID “IIRDB” :

Shared Memory:
ID              KEY
100270098 0x00000000
100302867 0x00000000
100335643 0x14203f64

Semaphores:
ID              KEY
No semaphore resources used
Oracle Instance not alive for sid “IIRDB”

Solution
On OS level, remove the orphaned shared memory segment using:

ipcrm -m <problem shared memory id>

ipcrm -m 100270098
ipcrm -m 100302867
ipcrm -m 100335643

$sqlplus ‘/as sysdba’

Connected to an idle instance.

SQL> startup Oracle instance started
Total System Global Area   10689474560 bytes
Fixed Size                     2237776 bytes
Variable Size               6375344816 bytes
Database Buffers            4294967296 bytes
Redo Buffers                  16924672 bytes
Oracle Instance Started.
Oracle Database Opened.

Advertisements
Standard
Troubleshooting

EXP – IMP: ORA-04031

The ORA-04031 error returns whenever oracle attempts to allocate a large piece of contiguous memory in the shared pool but fails to allocate. Oracle first flushes all objects that are not currently in use from the pool and the resulting free memory chunks are merged. If there is still not a single chunk large enough to satisfy the request the ORA-04031 error is returned.

Since in 11g automatic memory management in enabled and this allows the dba to reserve a pool of shared memory that is used to allocate the shared pool, the buffer cache, the java pool and the large pool with the single parameter SGA_TARGET. So simply increase of SGA_TARGET likely solve the problem.

UDE-04031: operation generated ORACLE error 4031
ORA-04031: unable to allocate 16 bytes of shared memory (“shared pool”,”SELECT job_id FROM v$datapum…”,”SQLA”,”tmp”)
ORA-06512: at “SYS.KUPV$FT_INT”, line 2904
ORA-06512: at “SYS.KUPC$QUE_INT”, line 572
ORA-25254: time-out in LISTEN while waiting for a message
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3263
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 4488
ORA-06512: at line 1

 

Solution
Let’s see the sga_target value.
SQL> show parameter sga_t
NAME TYPE VALUE
———————————— ———– ——————————
sga_target big integer 64G

And SGA_MAX_SIZE value,
SQL> show parameter sga_max
NAME TYPE VALUE
———————————— ———– ——————————
sga_max_size big integer 64G

We see these two parameter settings are low. So we increase it and restart database. As setting of SGA_TARGET is depend on SGA_MAX_SIZE and to change SGA_MAX_SIZE we need to bounce the database in order to effect.
SQL> alter system set sga_max_size=64G scope=spfile;

System altered.

SQL> alter system set sga_target=100G scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 1248768 bytes
Variable Size 96469504 bytes
Database Buffers 209715200 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.

SQL> show parameter sga_target

NAME TYPE VALUE
———————————— ———– ——————————
sga_target big integer 100G

problem is solved.

Standard
Oracle

How to give privilege to kill session without giving ‘alter system’ privilege

Step 1 : Create the procedure with sys user
create or replace procedure kill_session
(pn_sid number
,pn_serial number)
as
lv_user varchar2(30);
begin
select username into lv_user from v$session where sid = pn_sid and serial# = pn_serial;
if lv_user is not null and lv_user in (‘ERMANNKARA’) then
execute immediate ‘alter system kill session ”’||pn_sid||’,’||pn_serial||””;
else
raise_application_error(-20000,’Attempt to kill protected system session has been blocked.’);
end if;
end;
/

Procedure created.

you can modify the clause lv_user in accordingly to suit your need.
Above package allows user ERMANNKARA to kill sessions for only ERMANNKARA user.

Step 2: grant execute privilege to user
SQL> grant execute on sys.kill_session to ERMANNKARA;

Grant succeeded.

Step 3: create a synonym the user
SQL> create synonym ERMANNKARA.kill_session for sys.kill_session;

Synonym created.

DEMO:

ermannkara

Standard
Linux

How to check character set in Oracle

One of the requirements for samePage is that the character set should be UTF-8.

You can either check with your DBA or run the following SQL to determine whether your database character set is UTF-8. 

 
SELECT value$ FROM sys.props$ WHERE name = ‘NLS_CHARACTERSET’ ;


It should return the value AL32UTF8.

Another option is to run the following SQL :

 
SELECT * FROM NLS_DATABASE_PARAMETERS


The value against 
NLS_CHARACTERSET should be UTF8.

ee
Standard
Oracle, Troubleshooting

Why parallel expdp gives unable to write error DATAPUMP With PARALLEL > 1 ON 11.2 RAC

hello, Problem:

Datapump on 11.2 RAC with PARALLEL > 1  hits the following errors.

ORA-31693: Table data object “IIR_USER”.”IDS_00_INUSE” failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file “/export/expıırdb_04.dmp” for write
ORA-19505: failed to identify file “/export/expıırdb_04.dmp”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

 

Cause

From 11.2, Datapump new parameter CLUSTER is introduced.

CLUSTER : Default=Y

Purpose :
Determines whether Data Pump can use Oracle Real Application Clusters (RAC)
resources and start workers on other Oracle RAC instances.

Syntax and Description : CLUSTER=[Y | N]

Solution

To force Data Pump to use only the instance where the job is started and to replicate pre-Oracle Database 11g release 2 (11.2) behavior, specify CLUSTER=N.

export scripts :

[oracle@jery scripts]$ cat expIIRDB.sh
expdp userid=\”/ as sysdba\” DIRECTORY=EXP01 full=Y DUMPFILE=expıırdb_%U.dmp LOGFILE=expıırdb.log parallel=4 compression=all CLUSTER=N
[oracle@jery scripts]$

 

Br. Erman.

Standard
Oracle, Troubleshooting

Oracle 11g: DDL WAIT Option (DDL_LOCK_TIMEOUT)

Hello,In Oracle 11g, the new initialization parameter DDL_LOCK_TIMEOUT controls the duration (in seconds) for which a DDL statement will wait for a DML lock.The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.

The default value of zero indicates a status of NOWAIT.

SQL> show parameter DDL_LOCK_TIMEOUT;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
ddl_lock_timeout                     integer     0

If these locks are not available, the commands return with an “ORA-00054: resource busy” error message.

solutions:

  1.  kill oracle user session,
  2. Update DDL_LOCK_TIMEOUT
    SQL> set timing onSQL> alter session set ddl_lock_timeout=60;

    Session altered.
    Elapsed: 00:00:00.02

br Erman.

Standard
Oracle, Troubleshooting

ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device

hello,

problem : ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device

 

Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 981795
Additional information: -1
ORA-31693: Table data object "EBDYSLIVE"."VeriTarihcesi":"SYS_P726" failed to load/unload and is being skipped due to error:
ORA-31644: unable to position to block number 842047 in dump file "/oracle/export/EBDYS/expebdys_04.dmp"
ORA-19502: write error on file "/oracle/export/EBDYS/expebdys_04.dmp", block number 842047 (block size=4096)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 842047
Additional information: -1
ORA-31693: Table data object "EBDYSLIVE"."VeriTarihcesi":"SYS_P787" failed to load/unload and is being skipped due to error:
ORA-31644: unable to position to block number 981855 in dump file "/oracle/export/EBDYS/expebdys_03.dmp"
ORA-19502: write error on file "/oracle/export/EBDYS/expebdys_03.dmp", block number 981855 (block size=4096)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 981855
Additional information: -1
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.MAIN [TABLE_DATA:"SYSMAN"."MGMT_METRICS_1HOUR"]
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "SYS.KUPW$WORKER", line 10568
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "SYS.KUPW$WORKER", line 3425
ORA-24795: Illegal ROLLBACK attempt made
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "SYS.KUPW$WORKER", line 16524
ORA-19502: write error on file "/oracle/export/EBDYS/expebdys_02.dmp", block number 1006207 (block size=4096)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 1006207
Additional information: -1

solutions :

Solution:

Check the page size:
#getconf PAGE_SIZE
4096

Calculate proper value for shmall:

The value of shmall should be:

Shmall=total size of the SGAs on the system/page size.

Let’s assume the size of the SGA is 16GB in the system then it would be 1024 * 1024 * 1024 * 16 / 4096 = 4194304

Change shmall in /etc/sysctl.conf
vi /etc/sysctl.conf   

kernel.shmall = 4194304

Apply the changes:

# sysctl -p  

check shmall value after change
# sysctl -A | grep shmall

 

Standard