Feeds:
Posts
Comments

Posts Tagged ‘startup’

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….!!!!

Advertisements

Read Full Post »

STARTUP

Normal database operation means that an instance is started and the database is mounted and open. This mode allows any valid user to connect to the database and perform typical data access operations.

Start an instance, read the initialization parameters from the default server parameter file location, and then mount and open the database by using the STARTUP command by itself (you can, of course, optionally specify the PFILE clause):

STARTUP NOMOUNT

You can start an instance without mounting a database. Typically, you can do so only during database creation. Use the STARTUP command with the NOMOUNT clause:

STARTUP MOUNT

You can start an instance and mount a database without opening it, allowing you to perform specific maintenance operations. For example, the database must be mounted but not open during the following tasks:

  • Enabling and disabling redo log-archiving options.
  • Performing full database recovery.

Start an instance and mount the database, but leave it closed by using the STARTUP command with the MOUNT clause:

STARTUP FORCE

In unusual circumstances, you might experience problems when attempting to start a database instance. You should not force a database to start unless you are faced with the following:

  • You cannot shut down the current instance with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL commands.
  • You experience problems when starting an instance.

If one of these situations arises, you can usually solve the problem by starting a new instance (and optionally mounting and opening the database) using the STARTUP command with the FORCE clause:

STARTUP RESTRICT

You can start an instance, and optionally mount and open a database, in restricted mode so that the instance is available only to administrative personnel (not general database users). Use this mode of instance startup when you need to accomplish one of the following tasks:

  • Perform an export or import of database data
  • Perform a data load (with SQL*Loader)
  • Temporarily prevent typical users from using data
  • During certain migration and upgrade operations

Typically, all users with the CREATE SESSION system privilege can connect to an open database. Opening a database in restricted mode allows database access only to users with both the CREATE SESSION and RESTRICTED SESSION system privilege. Only database administrators should have the RESTRICTED SESSION system privilege. Further, when the instance is in restricted mode, a database administrator cannot access the instance remotely through an Oracle Net listener, but can only access the instance locally from the machine that the instance is running on.

Start an instance (and, optionally, mount and open the database) in restricted mode by using the STARTUP command with the RESTRICT clause:

Later, use the ALTER SYSTEM statement to disable the RESTRICTED SESSION feature:

ALTER SYSTEM DISABLE RESTRICTED SESSION;

STARTUP OPEN RECOVER

If you know that media recovery is required, you can start an instance, mount a database to the instance, and have the recovery process automatically start by using the STARTUP command with the RECOVER clause.

=====================================================================

SHUTDOWN NORMAL

To shut down a database in normal situations, use the SHUTDOWN command with the NORMAL clause:

Normal database shutdown proceeds with the following conditions:

  • No new connections are allowed after the statement is issued.
  • Before the database is shut down, the database waits for all currently connected users to disconnect from the database.

The next startup of the database will not require any instance recovery procedures.

SHUTDOWN IMMEDIATE

Use immediate database shutdown only in the following situations:

  • To initiate an automated and unattended backup
  • When a power shutdown is going to occur soon
  • When the database or one of its applications is functioning irregularly and you cannot contact users to ask them to log off or they are unable to log off

To shut down a database immediately, use the SHUTDOWN command with the IMMEDIATE clause:

Immediate database shutdown proceeds with the following conditions:

  • No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
  • Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)
  • Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly rolls back active transactions and disconnects all connected users.

The next startup of the database will not require any instance recovery procedures.

SHUTDOWN TRANSACTIONAL

When you want to perform a planned shutdown of an instance while allowing active transactions to complete first, use the SHUTDOWN command with the TRANSACTIONAL clause:

Transactional database shutdown proceeds with the following conditions:

  • No new connections or new transactions allowed starting after the statement is issued.
  • After all transactions have completed, any client still connected to the instance is disconnected.
  • At this point, the instance shuts down just as it would when a SHUTDOWN IMMEDIATE statement is submitted.

The next startup of the database will not require any instance recovery procedures.

A transactional shutdown prevents clients from losing work, and at the same time, does not require all users to log off.

SHUTDOWN ABORT

You can shut down a database instantaneously by aborting the database instance. If possible, perform this type of shutdown only in the following situations:

The database or one of its applications is functioning irregularly and none of the other types of shutdown works.

  • You need to shut down the database instantaneously (for example, if you know a power shutdown is going to occur in one minute).
  • You experience problems when starting a database instance.

When you must do a database shutdown by aborting transactions and user connections, issue the SHUTDOWN command with the ABORT clause:

An aborted database shutdown proceeds with the following conditions:

  • After issuing the statement, no new connection or transaction are allowed.
  • Current client SQL statements being processed by Oracle Database are immediately terminated.
  • Uncommitted transactions are not roll back.
  • Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly disconnects all connected users.

The next startup of the database will require instance recovery procedures.

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

Read Full Post »