Thursday 7 November 2013

You are hotbackup is running and suddenly database crashed/Someone killed pmon process then what will happen to database?

Terminal 1
=======
SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1318172 bytes
Variable Size             436208356 bytes
Database Buffers          620756992 bytes
Redo Buffers               13049856 bytes
Database mounted.
Database opened.

SQL> alter database begin backup;

Database altered.

SQL>  select count(*),status from v$backup group by status;

  COUNT(*) STATUS
---------- ------------------
        44 ACTIVE

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
  8  );

Table created.

SQL> !date
Fri Nov  8 02:13:33 IST 2013

SQL>
SQL> exit
ERROR:
ORA-03135: connection lost contact
Process ID: 0
Session ID: 400 Serial number: 7

Terminal 2
=======
[dbvision@apps ~]$ ps -fu $LOGNAME|grep -i pmon
dbvision  5448     1  0 01:55 ?        00:00:00 ora_pmon_vision
dbvision  5968  5931  0 02:11 pts/3    00:00:00 grep -i pmon
[dbvision@apps ~]$ kill -9 5448
[dbvision@apps ~]$ ps -fu $LOGNAME|grep -i pmon
dbvision  6025  5931  0 02:13 pts/3    00:00:00 grep -i pmon
[dbvision@apps ~]$ date
Fri Nov  8 02:14:02 IST 2013
[dbvision@apps ~]$


Starting the database after crashed
========================
SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1318172 bytes
Variable Size             436208356 bytes
Database Buffers          620756992 bytes
Redo Buffers               13049856 bytes
Database mounted.
ORA-10873: file 1 needs to be either taken out of backup mode or media
recovered
ORA-01110: data file 1: '/db/vision/db/apps_st/data/system01.dbf'


SQL>  select count(*),status from v$backup group by status;

  COUNT(*) STATUS
---------- ------------------
        44 ACTIVE

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> alter database end backup;

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> 1date
SP2-0042: unknown command "1date" - rest of line ignored.
SQL> !date
Fri Nov  8 02:16:38 IST 2013

SQL>


3 comments:

  1. *************************************************************************************

    select dbms_utility.data_block_address_file(218121099) "Rfile#"
    ,dbms_utility.data_block_address_block(218121099) "Block#"
    from dual;
    ********************************************************************************

    select owner, segment_name, segment_type
    from dba_segments
    where header_file = 52
    and header_block = 17291;
    *****************************************************************

    ReplyDelete
  2. Please check for the Corrupted blocks in the database ,

    There may be a chance of block corruption in the database when the abrubpt shutdown of the database.

    Thanks ,
    Shaik . Basha
    ORACLE APPS DBA

    ReplyDelete