ORA-01012: not logged on startup failed

Hi Guys recently we had issue while starting the DB of our Hyperion application.  Just wanted to share this with you guys which will help you.

Background:

Since 2 weeks while stopping DB it was taking more than 40 mins. This time it took more than 40 min. While checking the pending oracle process only one it was showing so I killed that one.

$ ps -ef | grep -i hyq

  orasid  3342396        1   0   Sep 16      –  0:15 /oracle/SID/112_64/bin/tnslsnr LISTENER -inherit

  orasid 36372574 51970254   1 06:14:07      –  0:07 oracleSID (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

$ kill -9 51970254

 

Problem:

Now while starting the DB instance.

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 25 07:23:45 2015

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

Connected.

SQL> startup

ORA-01012: not logged on

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ORA-01012: not logged on

So whenever we forcefully shut down the DB this issue may occurs

“SYSRESV”  shows a shared memory segment for a non-existing instance

 

Solution:

At OS level remove the orphaned shared memory segment using this utility

$ sysresv

IPC Resources for ORACLE_SID “SID” :

Shared Memory:

ID              KEY

1048579         0xffffffff

4               0xffffffff

5               0x6767020c

Oracle Instance not alive for sid “SID”

$ ipcrm -m 1048579

$ ipcrm -m 4

$ ipcrm -m 5

 

Now tried to start DB

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 25 07:29:17 2015

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 7482626048 bytes

Fixed Size                  2236048 bytes

Variable Size            3271557488 bytes

Database Buffers         4194304000 bytes

Redo Buffers               14528512 bytes

Database mounted.

Database opened.

SQL> exit

Now good to see that instance getting stop & start immediately without any delay. 

Hope this article helped to you. I am expecting your suggestions/feedback.

It will help to motivate me to write more articles….!!!!

Thanks & Regards,

Samadhan

https://samadhandba.wordpress.com/

“Key for success, always fight even knowing your defeat is certain….!!!!

Read Full Post »

ora-12505 connection refused the specified sid

Hi,today one developer got this error while connecting to new production server.

Problem:

User was not able to connect to production server throght the application the error was,

ORA-12505 Connection refused, the specified SID (retaildb) was not recognized by the Oracle server.”

C:Documents and SettingsAdministrator>tnsping Global

TNS Ping Utility for 32-bit Windows: Version 9.2.0.6.0 – Production on 03-JUN-20
11 12:43:33

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

Used parameter files:
E:oracleRILDEVnetworkadminsqlnet.ora

TNS-03505: Failed to resolve name

C:Documents and SettingsAdministrator>
C:Documents and SettingsAdministrator>
C:Documents and SettingsAdministrator>
C:Documents and SettingsAdministrator>
C:Documents and SettingsAdministrator>lsnrctl services

LSNRCTL for 32-bit Windows: Version 9.2.0.6.0 – Production on 03-JUN-2011 12:44:
26

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=romavser)(PORT=1521)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
  Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
    Handler(s):
      “DEDICATED” established:0 refused:0
         LOCAL SERVER
The command completed successfully

C:Documents and SettingsAdministrator>

 

And Listener.ora entry was :

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = romavser)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
    )
  )

Solution:

 had a service name “SAMDB”. When I tried connect to that service it always gave
ORA-12505, TNS:listener does not currently know of SID given in connect …

So I edited the listener.ora, added one more SID_DESC, restarted the listener. Now I can connect to the “SAMDB” database. Below is the updated listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = E:oracleSAMDEV)
      (PROGRAM = extproc)
    )

    (SID_DESC =
      (GLOBAL_DBNAME = SAMDB)
      (ORACLE_HOME = E:oracleSAMDEV)
      (SID_NAME = SAMDB)
     )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
     (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
     )
     (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.231.38.15)(PORT = 1521))
     )
 
 )
)

Restarted the listener & error got resolved.

Read Full Post »

ORA-01157: cannot identify/lock data file / ORA-01110: data file/ EXP-00056: ORACLE error 1157 encountered

Dear all, yesterday I face small issue which I would like to share with you guy’s.It was temporary tablespace issue.This was development server for storage was recently changed.As mount point was changed but control file was having the previous temp file details so got the error while exporting schema.Just drop the old tempfile and created new one. Issue got resolved after that.

bash-2.05$ ls -lrt

total 562420

-rw-r–r–   1 oracle10g oinstall    1729 Jan 30 14:57 abc.sh

-rw-r—–   1 oracle10g oinstall 77631488 Jan 30 23:50 reporter_sam_exp30JAN2011.dmp

-rw-r—–   1 oracle10g oinstall 77635584 Apr  9 23:19 reporter_sam_exp09042011.dmp

-rw-r—–   1 oracle10g oinstall 132513792 Jun  1 04:01 SAM1_exp31052011.dmp

bash-2.05$ exp

Export: Release 10.2.0.3.0 – Production on Wed Jun 1 04:13:42 2011

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

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

Enter array fetch buffer size: 4096 >

Export file: expdat.dmp > SAM1_orig_exp31052011.dmp

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 2

Export grants (yes/no): yes > y

Export table data (yes/no): yes > y

Compress extents (yes/no): yes > y

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users …

User to be exported: (RETURN to quit) > SAM1

User to be exported: (RETURN to quit) >

 . exporting pre-schema procedural objects and actions

. exporting foreign function library names for user SAM1

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user SAM1

About to export SAM1’s objects …

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

EXP-00056: ORACLE error 1157 encountered

ORA-01157: cannot identify/lock data file 201 – see DBWR trace file

ORA-01110: data file 201: ‘/oradata/oracle10g/oradata/temp/temp2.dbf’

EXP-00000: Export terminated unsuccessfully

bash-2.05$ sqlplus / as sysdba

 SQL*Plus: Release 10.2.0.3.0 – Production on Wed Jun 1 04:16:46 2011

 Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

 SQL> select * from v$tempfile;

      FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED

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

     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE

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

NAME

——————————————————————————–

         1       8.2892E+12 07-FEB-11         24          2 ONLINE  READ WRITE

         0          0   2147483648       8192

/oradata/oracle10g/oradata/temp/temp2.dbf

          3       8.2892E+12 07-FEB-11         24          1 ONLINE  READ WRITE

         0          0   3221225472       8192

/oradata/oracle10g/oradata/temp/temp1.dbf

      FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED

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

     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE

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

NAME

——————————————————————————–

 SQL> select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files;

select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files

                                                          *

ERROR at line 1:

ORA-01157: cannot identify/lock data file 203 – see DBWR trace file

ORA-01110: data file 203: ‘/oradata/oracle10g/oradata/temp/temp1.dbf’

 SQL> select file#,status,name from v$tempfile;

      FILE# STATUS

———- ——-

NAME

——————————————————————————–

         1 ONLINE

/oradata/oracle10g/oradata/temp/temp2.dbf

          3 ONLINE

/oradata/oracle10g/oradata/temp/temp1.dbf

 SQL> !ls -lrt /oradata/oracle10g/oradata/temp/temp1.dbf

/oradata/oracle10g/oradata/temp/temp1.dbf: No such file or directory

 SQL> !ls -lrt /oradata/oracle10g/oradata/temp/temp2.dbf

/oradata/oracle10g/oradata/temp/temp2.dbf: No such file or directory

 SQL> select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files;

select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files

                                                           *

ERROR at line 1:

ORA-01157: cannot identify/lock data file 203 – see DBWR trace file

ORA-01110: data file 203: ‘/oradata/oracle10g/oradata/temp/temp1.dbf’

 SQL> alter database tempfile ‘/oradata/oracle10g/oradata/temp/temp1.dbf’ drop;

 Database altered.

 SQL> select file#,status,name from v$tempfile;

      FILE# STATUS

———- ——-

NAME

——————————————————————————–

         1 ONLINE

/oradata/oracle10g/oradata/temp/temp2.dbf

 SQL> ALTER TABLESPACE TEMP1 add tempfile ‘/DATA1/temp1.dbf’ size 10G;

 Tablespace altered.

 SQL> select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files;

ERROR:

ORA-01157: cannot identify/lock data file 201 – see DBWR trace file

ORA-01110: data file 201: ‘/oradata/oracle10g/oradata/temp/temp2.dbf’

 SQL> alter database tempfile ‘/oradata/oracle10g/oradata/temp/temp2.dbf’ drop;

 Database altered.

 SQL> ALTER TABLESPACE TEMP1 add tempfile ‘/DATA1/temp2.dbf’ size 10G;

 Tablespace altered.

 SQL> select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files;

 TABLESPACE_NAME

——————————

FILE_NAME

——————————————————————————–

        MB

———-

TEMP1

/DATA1/temp1.dbf

     10240

 TEMP1

/DATA1/temp2.dbf

     10240

 SQL> select file#,status,name from v$tempfile;

      FILE# STATUS

———- ——-

NAME

——————————————————————————–

         1 ONLINE

/DATA1/temp2.dbf

          2 ONLINE

/DATA1/temp1.dbf

 SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

bash-2.05$ pwd

/DATA1

bash-2.05$ cd –

/DATA

bash-2.05$ cd sam_cxl

bash-2.05$ ls -lrt

total 562420

-rw-r–r–   1 oracle10g oinstall    1729 Jan 30 14:57 abc.sh

-rw-r—–   1 oracle10g oinstall 77631488 Jan 30 23:50 sam_cxl_exp30JAN2011.dmp

-rw-r—–   1 oracle10g oinstall 77635584 Apr  9 23:19 sam_cxl_exp09042011.dmp

-rw-r—–   1 oracle10g oinstall 132513792 Jun  1 04:01 SAM1_exp31052011.dmp

bash-2.05$ exp

 Export: Release 10.2.0.3.0 – Production on Wed Jun 1 04:35:18 2011

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

 Username: / as sysdba

 Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

Enter array fetch buffer size: 4096 >

 Export file: expdat.dmp > SAm1_orig_exp31052011.dmp

 (1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 2

 Export grants (yes/no): yes >

 Export table data (yes/no): yes >

 Compress extents (yes/no): yes >

 Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P1 character set (possible charset conversion)

 About to export specified users …

User to be exported: (RETURN to quit) > SAM1

 User to be exported: (RETURN to quit) >

 . exporting pre-schema procedural objects and actions

. exporting foreign function library names for user SAM1

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user SAM1

About to export ISCM’s objects …

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export SAM1’s tables via Conventional Path …

. . exporting table       SAM_BERTHING_GRADE_ARMS          5 rows exported

.

.

.

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully with warnings.

Hope you guy’s enjoy this post.Very soon I wil come up with Parsing & execution as well.

Experts are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

Reducing datafile size to recover free space / ORA-03297: file contains used data beyond requested RESIZE value

At time, we want to recover some space from database just to allocate the same to some other tablespace or to return it back to OS disk. This situation arises many times.
And many time we hit with error “ORA-03297: file contains used data beyond requested RESIZE value“.
The concept is simple and many of you must be knowing, but just putting in a simpler words.

Lets take an example of one of the datafile in a database.

Lets see the total free space in a datafile 194.

SQL> select sum(bytes)/1024/1024 from dba_free_space
2  where tablespace_name = ‘APPS_TS_TX_DATA’
3  and file_id = 194;

SUM(BYTES)/1024/1024
——————–
1844.125

Now lets see the distribution for the file.

SQL> select file_id, block_id, blocks, bytes,  ‘Free’ from dba_free_space
2  where tablespace_name = ‘APPS_TS_TX_DATA’
3  and file_id = 194
4  and rownum < 7
5  order by block_id desc;

   FILE_ID   BLOCK_ID     BLOCKS      BYTES ‘FRE
———- ———- ———- ———- —-
194      35001     220992 1810366464 Free
194      13433         16     131072 Free
194      13417         16     131072 Free
194      13401         16     131072 Free
194      13385         16     131072 Free
194      13369         16     131072 Free

We can see that there are so many blocks which are free.  Its divided into chunks of 16 Blocks and each of these chunks are given a block ID. Now we can see that this formating is done until block 35001 and after that there is no partitions. Beyond the block ID 35001, the whole space is available as a single large unit. This is because of high water mark. When a object is created, it will be created physically in the datafile and will be allocated a block. The number of blocks it will be allocated will depend on the parameter “INITIAL EXTENT” which can be given at the time of creating an object. If we dont give this parameter it will take a default value of 16. So 16 block gets assigned when you create any abject, example a table.

You might be wondering that after block 35001, we have all free space and also we have free space at blocks 13401, 13417, 13433 etc. But where are the blocks between 13433 and 35001??

The answer can be found from dba_extents. All the blocks between 13433 and 35001 are occupied by the objects and are not free. That why you are not able to see them in dba_free_space view. But you can find then in dba_extents. So in case of file 194, objects were getting created until block no 35001 (so we can see that block formating till block 35001) and then at later point of time some of the objects got dropped, so the space got freed, but the formating of blocks remain intact (example block IDs which got freed are 13401, 13417, 13433 etc.). This we call it as high water mark for a datafile.

As we saw earlier that we have around 1844.125 MB free space. Can we recover all of them?? I wish .. but no, we cannot. Reason being that, some of the blocks are free “in-between” and there is a fragmentation. To make my point clear, lets try to reduce the file size.

Lets try that !!

This file original size is 2000M

SQL> select bytes/1024/1024 from dba_data_files where file_id = 194;

BYTES/1024/1024
—————
2000

and as per dba_free_space its having 1844.125 MB free space. Lets reduce the file size by 1800 MB and it will definately give error.

SQL> alter database datafile 194 resize 200M;
alter database datafile 194 resize 200M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

What happened??

The reason you are not able to free the space is because we have certain objects created in between and there is no chunk of 1800MB free. The structure of this file is as given below.

X -> Occupied Block
0 -> Free Block

So here block 35001, 13433 are free, but in-between blocks are occupied. When you are trying to reduce the size of datafile by 1800 MB, you are going beyond block ID 35001, where the objects do exits. After those objects there may be free space, but that doesnt matter, you can at max reduce the size of datafile until block ID 35001, and not beyond that.

So here if we see that there are around 220992 blocks free after block ID 35001. That makes a space of around (220992 X 8192 = 1810366464 bytes = 1726.5 MB free). Thats what the bytes column is showing.
So we can reduce the size of this datafile by maximum of 1810366464 bytes = 172.5 MB. If you try to reduce the size more than that, then you will hit with an error ORA-03297: file contains used data beyond requested RESIZE value.

Now try reducing by 1700 MB and it will succeed !!!

SQL> alter database datafile 194 resize 300M;

database altered

What if you want to still reduce the size beyond 1800 MB. i.e. what if you want to make the size to 200MB.

To do that, you need to do following steps.

– Find block ID at 1800th byte.

We know that last 1810366464 bytes are free.
1800 MB = 1887436800 Bytes
Bytes Remaining = 1887436800 – 1810366464 = 77070336 Bytes = 9408 Blocks
Block ID till we want to clear the objects id  35001 – 9408 = 25593

– Drop all the objects which are beyond this block. That will make 1800MB free in this datafile and you can reduce the size of datafile by 1800 MB.

Moral of story is dont get into these many calculations. Its good to know the logic and helps in crisis situation. But not good to go for such drop object, reduce the size if file and recreate the object again.

Read Full Post »