Tag: oracle error
Opening the database with corrupted redo log
ORA-00333: redo log read error block 9233 count 2312
Opening the database with corrupted redo log can cause a loss of committed transactions, therefore, you need to do it at your own risk
Steps :
SQL> Startup Mount;
SQL> alter system set “_allow_resetlogs_corruption”=true scope=spfile;System altered.
SQL> shutdown immediate;
ORA-01109: database not openDatabase dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.Total System Global Area 281018368 bytes
Fixed Size 779000 bytes
Variable Size 229383432 bytes
Database Buffers 50331648 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> recover database until cancel;
ORA-00280: change 101350984923848 for thread 1 is in sequence #2333
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/home/oracle/oradata/rsdb/system01.dbf’
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
SQL>shutdown immediate;
SQL>Startup;
You can Do this Steps When Data Its Not Important , Such As Test Evn , ….
Enjoy
Osama Mustafa
ORA-01110 When trying to Open Database
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 5: ‘ /u01/test/users02.dbf’
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 5: ‘ /u01/test/users02.dbf’
This Error Maybe Appear On Different Database Files Because DBF Not Exists .
Solution
1-Restore From Rman if its Not Found .
Or
SQL> select file#,name, status from v$datafile where file#=5;
FILE# NAME STATUS
———- ————————————————- ———–
5 /u01/test/users02.dbf ONLINESQL>
SQL> alter database datafile ‘/u01/test/users02.dbf’ offline drop;
Database altered.SQL> alter database open;
Database altered.
SQL>
Thank you
Osama Mustafa
ORA-00020: maximum number of processes (%s) exceeded
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 150
SQL> select count(*) from v$process;
COUNT(*)
----------
149
SQL> alter system set processes=300 scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 300
Thank you
Osama mustafa
ORA-16038 ORA-19504
Archiver Hung in Oracle database
Summary
1. check how much space is used by archiver
2. check the last good backup of the database
3. delete archive log files older than last good backup
4. crosscheck archive log
1. check how much space is used by archiver
Sql> select count(*),sum(blocks*block_size) from v$archived_log where backup_count=0 and deleted=’NO’;
Sql> select * from v$flash_recovery_area_usage;
2. check the last good backup of the database
set pages 999 lines 120
col STATUS format a9col hrs format 999.99col start_time format a15col end_time format a15col dev format a5col inbytes format a10col outbytes format a10selectINPUT_TYPE, STATUS,to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time,to_char(END_TIME,’mm/dd/yy hh24:mi’) end_time,elapsed_seconds/3600 hrs,output_device_type dev,input_bytes_display inbytes,output_bytes_display outbytesfrom V$RMAN_BACKUP_JOB_DETAILSorder by session_key;
3. delete archive log files older than last good backup
rman target / nocatalog
allocate channel for maintenance device type disk;
crosscheck archivelog all;
delete noprompt archivelog until time ‘sysdate – 1’;
delete noprompt expired archivelog all;
delete noprompt obsolete device type disk;
4. crosscheck archive log
crosscheck archivelog all;
release channel;
exit;
release channel;
exit;
Thank you
Osama mustafa
ORA-19566: exceeded limit of 0 corrupt blocks for file
I already Talk about this Error but this Topics Share Another Solution For it :
RMAN> backup database;RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/22/2011 11:45:40
ORA-19566: exceeded limit of 0 corrupt blocks for file /oracle/oradata/orcl/system01.dbf
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 2684 1 0 CHECKSUM
RMAN> blockrecover datafile 5 block 2684;
Thank you
Osama mustafa
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 960
This Error Appear when you try to create MATERIALIZED VIEW On remote View
First you have to know it
Bug 5583712 ORA-942 on create materialized view on remote view
| Product (Component) | Oracle Server (Rdbms) |
| Range of versions believed to be affected | Versions < 11 |
| Versions confirmed as being affected | |
| Platforms affected | Generic (all / most platforms affected |
You can check Metalink Doc : 5583712.8
But I would Share this solution All you Have to do is follow the below steps :
The Solution is :
Thank you
Osama mustafa
FRM-92102 : A network error has occured
FRM-92102 is Gerneric Error maybe occur for more than one reasons :
1-Network
2-Proxy
3-http
4-Session Time
But today i will discuss the problem On oracle Application server 10g .
Description for the problem like the following when you try to connect on your deploy application On OAS 10g it’s gives the above error from 1-5 minutes.
I will give you more than one solution maybe it will be related to the above problem and you try them separately to see which one will be valid for you :
1-Netowrk Parameters :
You will find it $ORACLE_HOME/forms/server/default.env
Just increase the value .
2-do the following change in opmn.xml (under $ORACLE_HOME/opmn/conf/):
3-SET Inbound_connection_timeout In sqlnet.ora to ZERO .
Sqlnet.Inbound_connection_timeout = 0
Note : if your can’t find this parameter in the SQLNET.ORA you cant add it .
5- Change the following $ORACLE_HOME/opmn/conf/opmn.xml
Hope this will work
Thank you
Osama mustafa
ORA-12528 tns listener all appropriate instances are blocking new connections
SQL> set ORACLE_SID=ORCL
SQL> startup nomount
ORACLE instance started.
Total System Global Area 263639040 bytes
Fixed Size 1332552 bytes
Variable Size 222300856 bytes
Database Buffers 33554432 bytes
Redo Buffers 6451200 bytes
C:\>lsnrctl services
LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production
Service "ORCL" has 2 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:59
LOCAL SERVER
Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "ORCL_XPT" has 1 instance(s).
Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Want Solution Do the following :
you can add the new TNS connect string (UR = A) to the tnsnames entry.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
(UR = A)
)
)
if the above Solution not working Make sure your listener look like :
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app\oraserver\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\oraserver\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(SID_NAME=orcl)
(ORACLE_HOME=D:\app\oraserver\product\11.2.0\dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
Thank you
Osama Mustafa
Technology Geek 
