ORA-01157 and ORA-01110 Workaround for NOARCHIVELOG Mode Database

 

OS: RHEL

DB: Oracle Version 10.2.0.1.0

Alert log status

[oracle@myserver ~]$ tail -100f /u01/app/oracle/admin/orcl/bdump/alert_orcl.log
Shutting down instance (immediate)
License high water mark = 3
Thu Nov  8 12:14:49 2012
Stopping Job queue slave processes, flags = 7
Thu Nov  8 12:14:49 2012
Job queue slave processes stopped
Waiting for dispatcher ‘D000’ to shutdown
All dispatchers and shared servers shutdown
Thu Nov  8 12:14:51 2012
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL…
Thu Nov  8 12:14:51 2012
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thu Nov  8 12:14:58 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as /u01/app/oracle/product/10.2.0/db_1/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =121
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.4.0.
System parameters with non-default values:
processes                = 1000
sessions                 = 1105
__shared_pool_size       = 788529152
__large_pool_size        = 16777216
__java_pool_size         = 16777216
__streams_pool_size      = 33554432
nls_length_semantics     = BYTE
resource_manager_plan    =
sga_target               = 1258291200
control_files            = /u01/app/oracle/oradata/orcl/control01.ctl, /u01/app/oracle/oradata/orcl/control02.ctl, /u01/app/oracle/oradata/orcl/control03.ctl
db_block_size            = 8192
__db_cache_size          = 385875968
compatible               = 10.2.0.1.0
db_file_multiblock_read_count= 16
undo_management          = AUTO
undo_tablespace          = UNDOTBS1
undo_retention           = 900
remote_login_passwordfile= EXCLUSIVE
db_domain                =
dispatchers              = (PROTOCOL=TCP) (SERVICE=orclXDB)
job_queue_processes      = 10
background_dump_dest     = /u01/app/oracle/admin/orcl/bdump
user_dump_dest           = /u01/app/oracle/admin/orcl/udump
core_dump_dest           = /u01/app/oracle/admin/orcl/cdump
audit_file_dest          = /u01/app/oracle/admin/orcl/adump
db_name                  = orcl
open_cursors             = 300
pga_aggregate_target     = 414187520
_optimizer_connect_by_cost_based= FALSE
aq_tm_processes          = 0
PMON started with pid=2, OS id=11684
PSP0 started with pid=3, OS id=11686
MMAN started with pid=4, OS id=11688
DBW0 started with pid=5, OS id=11690
LGWR started with pid=6, OS id=11692
CKPT started with pid=7, OS id=11694
SMON started with pid=8, OS id=11696
RECO started with pid=9, OS id=11698
CJQ0 started with pid=10, OS id=11700
MMON started with pid=11, OS id=11702
Thu Nov  8 12:14:58 2012
starting up 1 dispatcher(s) for network address ‘(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))’…
MMNL started with pid=12, OS id=11704
Thu Nov  8 12:14:58 2012
starting up 1 shared server(s) …
Thu Nov  8 12:14:58 2012
ALTER DATABASE   MOUNT
Thu Nov  8 12:15:02 2012
Setting recovery target incarnation to 1
Thu Nov  8 12:15:02 2012
Successful mount of redo thread 1, with mount id 2714131202
Thu Nov  8 12:15:02 2012
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Thu Nov  8 12:15:02 2012
ALTER DATABASE OPEN
Thu Nov  8 12:15:02 2012
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_11690.trc:
ORA-01157: cannot identify/lock data file 33 – see DBWR trace file
ORA-01110: data file 33: ‘/u01/app/oracle/oradata/orcl/netvertex640.dbf’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN…

[oracle@aaa-build ~]$
[oracle@aaa-build ~]$
[oracle@aaa-build ~]$

file not found in os

[oracle@aaa-build ~]$ ll /u01/app/oracle/oradata/orcl/netvertex640.dbf
ls: /u01/app/oracle/oradata/orcl/netvertex640.dbf: No such file or directory

db status:-

[oracle@aaa-build ~]$
[oracle@aaa-build ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Nov 8 12:19:25 2012

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
———-
MOUNTED

Solution:-


SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence     1527
Current log sequence           1529
SQL>
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/netvertex640.dbf' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

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

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Dat

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.