Problem Description
Operating system version is IBM AIX on POWER Systems (64-bit), oracle version is 10.2.0.4 or higher and users are getting from Oracle OS based error messages: ORA-27300 ORA-27301 ORA-27302 ORA-27303.
ksvcreate: Process(m000) creation failed
Wed Mar 16 11:13:26 2011
Process startup failed, error stack:
Wed Mar 16 11:13:26 2010
Errors in file /u01/product/10.2.0/admin/a/bdump/a_psp0_1314923.trc:
ORA-27300: OS system dependent operation:fork failed with status: 2
ORA-27301: OS failure message: No such file or directory
ORA-27302: failure occurred at: skgpspawn5
ORA-27303: additional information: skgpspawn5
Cause of the Problem
The skgpspawn5 OS error message was due to an insufficient value for maxuproc of 128 on AIX.
Solution of the Problem
Increase the value for the AIX maxuproc (OS parameter) from 128 to 1024.
Operating system version is IBM AIX on POWER Systems (64-bit), oracle version is 10.2.0.4 or higher and users are getting from Oracle OS based error messages: ORA-27300 ORA-27301 ORA-27302 ORA-27303.
ksvcreate: Process(m000) creation failed
Wed Mar 16 11:13:26 2011
Process startup failed, error stack:
Wed Mar 16 11:13:26 2010
Errors in file /u01/product/10.2.0/admin/a/bdump/a_psp0_1314923.trc:
ORA-27300: OS system dependent operation:fork failed with status: 2
ORA-27301: OS failure message: No such file or directory
ORA-27302: failure occurred at: skgpspawn5
ORA-27303: additional information: skgpspawn5
Cause of the Problem
The skgpspawn5 OS error message was due to an insufficient value for maxuproc of 128 on AIX.
Solution of the Problem
Increase the value for the AIX maxuproc (OS parameter) from 128 to 1024.
Posted by Arju at 5:31 AM 0
comments 
Labels: OS, Troubleshooting, UNIX
Labels: OS, Troubleshooting, UNIX|
Reactions:
|
Problem Description
We have active data guard setup in our 11.2.0.1 version of Oracle database. It is upgraded to version 11.2.0.2 and now we see this ORA-1555 on the Standby Database. We can't logon to database with system, dbsnmp or other database users. Only sys as sysdba is working. Also we have investigated that we are running on system's undo Tablespace, - and not the one which has been created for undo.
From the alert log following entries are generated:
We have active data guard setup in our 11.2.0.1 version of Oracle database. It is upgraded to version 11.2.0.2 and now we see this ORA-1555 on the Standby Database. We can't logon to database with system, dbsnmp or other database users. Only sys as sysdba is working. Also we have investigated that we are running on system's undo Tablespace, - and not the one which has been created for undo.
From the alert log following entries are generated:
Wed Mar 16 11:48:22 2011
ORA-01555 caused by SQL statement below (SQL ID: dskjv8dgqdax0,
Query Duration=8519 sec, SCN: 0x03cd.7fb9ca61):
SELECT TRANSMAIN.RECSEQNO,TRANSMAIN.TRANSIK,TRANSMAIN.TRAUNIEX,TRANSMAIN.TRANSEX,TRANSMAIN.DEPKEY,TRANSMAIN.SECIK,TRANSMAIN.PORIK,TRANSMAIN.CTP,TRANSMAIN.DEALER,TRANSMAIN.TRASPECIK,TRANSMAIN.CDYIK,TRANSMAIN.BLKIK,TRANSMAIN.TRCELMNO,TRANSMAIN.TRCBUSNO,TRANSMAIN.TRASTATREQ,TRANSMAIN.TRASTATACT,TRANSMAIN.TRASTATREV,TRANSMAIN.TRACAN,TRANSMAIN.BLKDATE,TRANSMAIN.TRANSORG,TRANSMAIN.TRANOLINK,TRANSMAIN.XI_TRANSCOSTTAX,TRANSMAIN.XI_TRANSSETTLE,TRANSMAIN.XI_TRANSINSTRUC,TRANSMAIN.XI_BALBOOKPFC,TRANSMAIN.NOMVAL,TRANSMAIN.NOMVALEX,TRANSMAIN.AGRDATE,TRANSMAIN.INTBEGDATE,TRANSMAIN.STLMDATE,TRANSMAIN.PMTDATE,TRANSMAIN.TRAPRICE,TRANSMAIN.TRAYIELD,TRANSMAIN.CURVALQC,TRANSMAIN.CURVALPC,TRANSMAIN.ACRINTQC,TRANSMAIN.ACRINTPC,TRANSMAIN.STLMAMTSC,TRANSMAIN.FXRATEQP,TRANSMAIN.FXRATEQS,TRANSMAIN.ACRINTDAYS,TRANSMAIN.STLMCUR,TRANSMAIN.EXTNOTEDATE,TRANSMAIN.EXTNOTEID,TRANSMAIN.BOOKMONTH,TRANSMAIN.FXRATEIP,TRANSMAIN.ACRINTSC,TRANSMAIN.AGIODISASC,TRANSMAIN.CAPCHGID,TRANSMAIN.REFIRATE,TRANSMAIN.FROMDATE,TRANSMAIN.TODATE,TRANSMAIN.EXCTYPE
Wed Mar 16 11:59:29 2011
Thread 1 cannot allocate new log, sequence 13253
Private strand flush not complete
Current log# 4 seq# 17780 mem# 0: /u02/redo/redo401.log
Thread 1 advanced to log sequence 13253 (LGWR switch)
Current log# 1 seq# 13253 mem# 0: /u02/redo/redo101.log
Wed Mar 16 11:59:32 2011
LNS: Standby redo logfile selected for thread 1 sequence 13253 for
destination LOG_ARCHIVE_DEST_2
Wed Mar 16 11:59:34 2011
Archived Log entry 35541 added for thread 1 sequence 17780 ID
0x21653caa dest 1:
Wed Mar 16 12:02:20 2011
ALTER SYSTEM ARCHIVE LOG
Wed Mar 16 12:02:20 2011
Thread 1 cannot allocate new log, sequence 13254
Private strand flush not complete
Current log# 1 seq# 13253 mem# 0: /u02/redo/redo101.log
Thread 1 advanced to log sequence 13254 (LGWR switch)
Current log# 2 seq# 13254 mem# 0: /u02/redo/redo201.log
Archived Log entry 35543 added for thread 1 sequence 13253 ID
0x21653caa dest 1:
Cause of the Problem
According to Oracle support document ID 1273808.1 "ORA-01555 on Active Data Guard Standby Database" raised due to Oracle bug -
Bug 10320455: QUERY IN ACTIVE DATA GUARD RETURNS ORA-1555
which is duplicate from
Bug 10092353: ACTIVE DATAGUAD INSTANCE HANGS DUE TO A LIBRARY CACHE LOCK HOLDER
Solution of the Problem
To solve this bug you have to apply the Patch 10018789 from Oracle support for your Platform on Top of 11.2.0.2.
Posted by Arju at 1:22 AM 0
comments Labels: Data
Guard, Troubleshooting
Labels: Data
Guard, Troubleshooting|
Reactions:
|
Wednesday, March 9, 2011
Scenario of the Problem
It was changed your Oracle database name by following topic Change Database Name and DBID.
From the logfile we see.
It was changed your Oracle database name by following topic Change Database Name and DBID.
From the logfile we see.
DBNEWID: Release 10.1.0.2.0 - Production
Copyright (c) 2001, 2004, Oracle.
All rights reserved.
Connected to database A (DBID=3980222861)
Connected to server version 10.1.0
Control Files in database:
D:\DATAFILE\A\CONTROL01.CTL
D:\DATAFILE\A\CONTROL02.CTL
D:\DATAFILE\A\CONTROL03.CTL
Changing database name from A to B
Control File D:\DATAFILE\A\CONTROL01.CTL - modified
Control File D:\DATAFILE\A\CONTROL02.CTL - modified
Control File D:\DATAFILE\A\CONTROL03.CTL - modified
Datafile D:\DATAFILE\A\SYSTEM01.DBF - wrote new name
Datafile D:\DATAFILE\A\UNDOTBS01.DBF - wrote new name
Datafile D:\DATAFILE\A\SYSAUX01.DBF - wrote new name
Datafile X:\A\DATAFILE\O1_MF_T_6ODFXTNZ_.DBF - wrote new name
Datafile C:\WINDOWS\SYSTEM32\A\DATAFILE\O1_MF_RIJU_6ODGDM98_.DBF -
wrote new name
Datafile C:\A\DATAFILE\O1_MF_RIJU2_6ODGHW98_.DBF - wrote new name
Datafile C:\WINDOWS\SYSTEM32\A\DATAFILE\O1_MF_RIJU3_6ODGLDCQ_.DBF
- wrote new name
Datafile
C:\WINDOWS\SYSTEM32\A\DATAFILE\O1_MF_TEST_TBS_6OLJD53F_.DBF - wrote new name
Datafile C:\WINDOWS\SYSTEM32\A\DATAFILE\O1_MF_TOOLS_6P2DLVSD_.DBF
- wrote new name
Datafile D:\DATAFILE\A\TEMP01.DBF - wrote new name
Datafile
C:\WINDOWS\SYSTEM32\A\DATAFILE\O1_MF_TEST_TEM_6OLJMDYR_.TMP - wrote new name
Control File D:\DATAFILE\A\CONTROL01.CTL - wrote new name
Control File D:\DATAFILE\A\CONTROL02.CTL - wrote new name
Control File D:\DATAFILE\A\CONTROL03.CTL - wrote new name
Instance shut down
Database name changed to B.
Modify parameter file and generate a new password file before
restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
Now we are connected to database as sysdba and whenever startup is issued ORA-01103 is raised.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area
285212672 bytes
Fixed Size
788532 bytes
Variable Size
108001228 bytes
Database Buffers
176160768 bytes
Redo Buffers
262144 bytes
ORA-01103: database name 'B' in controlfile is not 'A'
Cause of the Problem
Within the nid generated log file we see it is said "Modify parameter file and generate a new password file before restarting". So before restarting database you must change the DB_NAME initialization parameter within pfile or spfile. You can also look How to avoid of recreating pfile or spfile if any error in spfile if you have only spfile.
Solution of the Problem
As we have spfile and database can be started in nomount stage using spfile we can do the following.
- Connect as sysdba and startup nomount.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area
314572800 bytes
Fixed Size
1290328 bytes
Variable Size
285216680 bytes
Database Buffers
20971520 bytes
Redo Buffers
7094272 bytes
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> select status from v$instance;
STATUS
------------
STARTED
- Check db_name parameter value in spfile.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
db_name string a
- Change it to b as we changed using nid tool.
SQL> alter system set db_name=b scope=spfile;
System altered.
- Shutdown the database.
SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
- Startup the instance
SQL> startup
ORACLE instance started.
Total System Global Area
285212672 bytes
Fixed Size
788532 bytes
Variable Size
108001228 bytes
Database Buffers
176160768 bytes
Redo Buffers
262144 bytes
ORA-01991: invalid password file 'X:\ORACLE\DATABASE\PWDa.ORA'
Now we see password file problem. So create a new password file.
C:\Documents and Settings\rijui>orapwd file=x:\oracle\dataabase\pwdb.ora entries=5 password=a
OPW-00001: Unable to open password-file
It fails since file path is incorrect. So I have corrected the path.
C:\Documents and Settings\rijui>orapwd file=x:\oracle\database\pwdb.ora entries=5 password=a
- Now connect as sysdba and open the database.
C:\Documents and Settings\rijui>orapwd file=x:\oracle\dataabase\pwdb.ora entries=5 password=a
OPW-00001: Unable to open password-file
It fails since file path is incorrect. So I have corrected the path.
C:\Documents and Settings\rijui>orapwd file=x:\oracle\database\pwdb.ora entries=5 password=a
- Now connect as sysdba and open the database.
C:\Documents and Settings\rijui>sqlplus / as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Mar 9 21:22:57
2011
Copyright (c) 1982, 2004, Oracle.
All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -
Production
With the Partitioning, OLAP and Data Mining options
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL> alter database open;
Database altered.
- Ensure that db name is changed.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
db_name string B
Posted by Arju at 6:31 PM 0
comments Labels: Troubleshooting
Labels: Troubleshooting|
Reactions:
|
Saturday, March 5, 2011
Problem Description
While dropping a tablespace it fails with error ORA-29857 like below.
SQL> drop tablespace example including contents and datafiles cascade constraints;
drop tablespace example including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace
Cause of the Problem
The problem arises because there was an attempt to drop a tablespace which contains secondary objects and/or domain indexes.
Solution of the Problem
Drop the domain indexes on the tablespace which you are going to delete. Also, find the domain indexes which created secondary objects in this tablespace and drop them. Then try dropping the tablespace.
You can find whether tablespace EXAMPLE contains any domain index or not by running query,
SQL> select index_name from dba_indexes where index_type = 'DOMAIN' and tablespace_name = 'EXAMPLE';
no rows selected
which means it does not have any domain index. Next you should look for secondary objects. However if you want to drop a tablepsace and if you think the user's objects in the tablespace is no longer needed you can try deleting all objects in that tablespace before dropping tablespace. You can also look for the list of users contain objects inside specified tablespace.
You can check it by,
SQL> select distinct owner from dba_tables where tablespace_name='EXAMPLE';
OWNER
------------------------------
HR
OE
PM
SH
IX
If you think you no longer need these user you can try to delete users.
SQL> drop user HR casecade;
User dropped.
SQL> drop user OE casecade;
User dropped.
SQL> drop user sh cascade;
User dropped.
SQL> drop user oe cascade;
User dropped.
SQL> drop tablespace example including contents;
drop tablespace example including contents
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
SQL> drop user ix cascade;
User dropped.
SQL> drop tablespace example including contents;
Tablespace dropped.
While dropping a tablespace it fails with error ORA-29857 like below.
SQL> drop tablespace example including contents and datafiles cascade constraints;
drop tablespace example including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace
Cause of the Problem
The problem arises because there was an attempt to drop a tablespace which contains secondary objects and/or domain indexes.
Solution of the Problem
Drop the domain indexes on the tablespace which you are going to delete. Also, find the domain indexes which created secondary objects in this tablespace and drop them. Then try dropping the tablespace.
You can find whether tablespace EXAMPLE contains any domain index or not by running query,
SQL> select index_name from dba_indexes where index_type = 'DOMAIN' and tablespace_name = 'EXAMPLE';
no rows selected
which means it does not have any domain index. Next you should look for secondary objects. However if you want to drop a tablepsace and if you think the user's objects in the tablespace is no longer needed you can try deleting all objects in that tablespace before dropping tablespace. You can also look for the list of users contain objects inside specified tablespace.
You can check it by,
SQL> select distinct owner from dba_tables where tablespace_name='EXAMPLE';
OWNER
------------------------------
HR
OE
PM
SH
IX
If you think you no longer need these user you can try to delete users.
SQL> drop user HR casecade;
User dropped.
SQL> drop user OE casecade;
User dropped.
SQL> drop user sh cascade;
User dropped.
SQL> drop user oe cascade;
User dropped.
SQL> drop tablespace example including contents;
drop tablespace example including contents
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
SQL> drop user ix cascade;
User dropped.
SQL> drop tablespace example including contents;
Tablespace dropped.
Posted by Arju at 5:50 AM 0
comments Labels: SQL, Troubleshooting
Labels: SQL, Troubleshooting|
Reactions:
|
Saturday, January 8, 2011
Problem Description
The status of OLAP Catalog in dba_registry is invalid. You can check the status of OLAP components in dba_registry using the following query:
The status of OLAP Catalog in dba_registry is invalid. You can check the status of OLAP components in dba_registry using the following query:
SQL> col comp_name for a20
SQL> select comp_name, status, version
2 from DBA_REGISTRY
3 where comp_name= 'OLAP Catalog';
COMP_NAME STATUS VERSION
-------------------- -----------
-----------------
OLAP Catalog
INVALID 10.2.0.3.0
Cause of the Problem
The problem happened due to either Oracle database upgrade bug or OLAP Option has not been installed properly.
Solution of the Problem
1. Try re-compiling the Invalid Objects by running the following SQL script:
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
Then, check the status of OLAP Catalog in dba_registry by running
following script:
SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry where COMP_NAME LIKE '%OLAP%';
2. If Step 1 does not make OLAP Catalog Valid, then run the following scripts:
SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry where COMP_NAME LIKE '%OLAP%';
2. If Step 1 does not make OLAP Catalog Valid, then run the following scripts:
SQL> SPOOL olap_validate.log
SQL> @$ORACLE_HOME/olap/admin/amdrelod.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> SPOOL OFF
After you run these scripts review the spool output
olap_validate.log file to check whether there are any errors while executing
the script.
3. Check for the invalid OLAP objects under SYS schema which are valid under OLAPSYS schema by running the following query and delete these invalid OLAP objects from SYS schema.
3. Check for the invalid OLAP objects under SYS schema which are valid under OLAPSYS schema by running the following query and delete these invalid OLAP objects from SYS schema.
SQL> select a.object_name,a.object_type,a.status
"SYS",b.status "OLAPSYS"
from dba_objects a, dba_objects b
where a.owner = 'SYS'
and b.owner = 'OLAPSYS'
and a.object_name = b.object_name
and a.object_type = b.object_type
and a.status <> 'VALID'
and b.status <> 'INVALID'
order by a.status;
4. Verify the status of OLAP Catalog in dba_registry as follows:
SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry
where COMP_NAME LIKE '%OLAP%;
Posted by Arju at 7:40 AM 0
comments Labels: Troubleshooting
Labels: Troubleshooting|
Reactions:
|
Sunday, January 2, 2011
Problem Description
Any $_GET request does not show expected result rather it forwarded to homepage of the site. For example I am in a subdirectory of a website and then I submit a form after filling the information. After I submit the form it is forwarded to homepage. More specifically, please openhttp://blastyourblog.com/seo-tool-04/alexacomparison.php and now I type two urls like http://arjudba.blogspot.com/ andhttp://arjudba.blogspot.com/ then it will display url ashttp://blastyourblog.com/seo-tool-04/alexacomparison.php?domains=arjudba.blogspot.com%0D%0Abanscam.blogspot.com&width=500&height=400>ype=t&tspan=3m and immediately it goes to homepage instead of showing result in that window. (Note that it is corrected now.) This is really pain problem as I see there is no code bug and also whenever I put the same script into home directory it works as usual.
Solution of the Problem
The problem happened due to my hosting server which is Hostgator in this case. According to hostgator http://blastyourblog.com/seo-tool-04/alexacomparison.php scenario was caused by a mod_security rule being flagged by the script. They said now your domain has been white listed for the mod_security rule and so it is functioning properly. So, if you face such type of problem in your server immediately contact to your hosting server instead of spending time in your end. They will edit the mod_security rule and then you will have no issue.
Any $_GET request does not show expected result rather it forwarded to homepage of the site. For example I am in a subdirectory of a website and then I submit a form after filling the information. After I submit the form it is forwarded to homepage. More specifically, please openhttp://blastyourblog.com/seo-tool-04/alexacomparison.php and now I type two urls like http://arjudba.blogspot.com/ andhttp://arjudba.blogspot.com/ then it will display url ashttp://blastyourblog.com/seo-tool-04/alexacomparison.php?domains=arjudba.blogspot.com%0D%0Abanscam.blogspot.com&width=500&height=400>ype=t&tspan=3m and immediately it goes to homepage instead of showing result in that window. (Note that it is corrected now.) This is really pain problem as I see there is no code bug and also whenever I put the same script into home directory it works as usual.
Solution of the Problem
The problem happened due to my hosting server which is Hostgator in this case. According to hostgator http://blastyourblog.com/seo-tool-04/alexacomparison.php scenario was caused by a mod_security rule being flagged by the script. They said now your domain has been white listed for the mod_security rule and so it is functioning properly. So, if you face such type of problem in your server immediately contact to your hosting server instead of spending time in your end. They will edit the mod_security rule and then you will have no issue.
Posted by Arju at 11:25 PM 0
comments Labels: PHP, Troubleshooting
Labels: PHP, Troubleshooting|
Reactions:
|
Saturday, December 11, 2010
Problem Description
Oracle data pump import operation is working fine without PARALLEL parameter. But whenever we specify PARALLEL parameter within the import statement it fails with following error:
Oracle data pump import operation is working fine without PARALLEL parameter. But whenever we specify PARALLEL parameter within the import statement it fails with following error:
ORA-39029: worker 3 with process name "DW03" prematurely
terminated
ORA-31671: Worker process DW03 had an unhandled exception.
ORA-39078: unable to dequeue message for agent
KUPC$A_1_20101212003722 from queue
"KUPC$C_1_20101212002622"
ORA-06512: at "SYS.KUPW$WORKER", line 1397
ORA-06512: at line 2
The import statement was following:
impdp
system/password
Schemas=PROD
Parallel=4 -------> We
are performing parallel data pump import
DIRECTORY=DATA_PUMP_DIR
LOGFILE=import.log
DUMPFILE= exp_part01.dmp, exp_part02.dmp, exp_part03.dmp,
exp_part04.dmp
Cause of the Problem
The problem happened due to insufficient STEAMS_POOL_SIZE parameter settings. In the impdp statement the PARALLEL parameter value greater than 1 needs more memory in STREAMS_POOL_SIZE than PARALLEL=1.
Solution of the Problem
Change the STEAMS_POOL_SIZE parameter to a higher value. You can change memory allocation of STREAMS_POOL_SIZE parameter as follows: (Note that 100MB is a safe estimate for the required parameter setting)
SQL> alter system set STREAMS_POOL_SIZE=100M;
And then retry the Data pump import operation
Posted by Arju at 1:07 AM 0
comments Labels: Data
Pump, Troubleshooting
Labels: Data
Pump, Troubleshooting|
Reactions:
|
Problem Description
While doing Oracle Data pump export operation following errors are reported.
While doing Oracle Data pump export operation following errors are reported.
.. exported "PROD"."THERAP" 0 KB 0 rows
UDE-00008: operation generated ORACLE error 39078
ORA-39078: unable to dequeue message for agent
KUPC$A_3_20101209232757 from queue "KUPC$S_3_20101209232756"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 558
ORA-25205: the QUEUE SYS.KUPC$S_3_20101209232756 does not exist
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2745
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3712
ORA-06512: at line 1
Cause of the Problem
Oracle Data pump uses streams to generate the export. So if the STREAMS_POOL_SIZE is too small or if it set to zero then it will report above error.
Solution of the Problem
Change the STEAMS_POOL_SIZE parameter to a higher value. You can change memory allocation as follows: (Note that 100MB is a safe estimate for the required parameter setting)
SQL> alter system set STREAMS_POOL_SIZE=100M;
And then retry the Data pump export operation.
Posted by Arju at 12:23 AM 0
comments Labels: Data
Pump, Troubleshooting
Labels: Data
Pump, Troubleshooting|
Reactions:
|
Thursday, December 9, 2010
Problem Description
In RAC database setting a memory parameter fails with ORA-32018. For example whenever we try to set streams_pool_size to a value then it fails like below.
SQL> alter system set streams_pool_size=100M;
alter system set streams_pool_size=100M
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance
SQL> alter system set streams_pool_size=100M sid='*';
alter system set streams_pool_size=100M sid='*'
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance
Cause of the Problem
The error message indicates that may be in one instance in RAC database parameter can be set successfully but in another instance it fails and overall you can't set the parameter across all instances.
Solution of the Problem
In order to know the instance which is causing the failure, try to set the parameter individually on each instance using the SID clause of the alter system command. Ensure the instance name by,
In RAC database setting a memory parameter fails with ORA-32018. For example whenever we try to set streams_pool_size to a value then it fails like below.
SQL> alter system set streams_pool_size=100M;
alter system set streams_pool_size=100M
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance
SQL> alter system set streams_pool_size=100M sid='*';
alter system set streams_pool_size=100M sid='*'
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance
Cause of the Problem
The error message indicates that may be in one instance in RAC database parameter can be set successfully but in another instance it fails and overall you can't set the parameter across all instances.
Solution of the Problem
In order to know the instance which is causing the failure, try to set the parameter individually on each instance using the SID clause of the alter system command. Ensure the instance name by,
SQL> show parameter instance
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
active_instance_count integer
cluster_database_instances integer 3
instance_groups string
instance_name string ORA3
instance_number integer 3
instance_type string RDBMS
open_links_per_instance integer 4
parallel_instance_group string
parallel_server_instances integer 3
From the parameter value we see we are in 3 nodes RAC database and
our instance name is ORA3. So set the parameter in our current instance by
specifying a SID value.
SQL> alter system set streams_pool_size=100M sid='ORA3';
System altered.
Similarly set it on rest of the instances ORA2 and ORA1 for example.
SQL> alter system set streams_pool_size=100M sid='ORA2';
System altered.
SQL> alter system set streams_pool_size=100M sid='ORA1';
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool
So we see ORA1 instance is causing the problem. This is because there is insufficient memory in ORA1 instance so you might try to increase SGA_TARGET/MEMORY_TARGET parameter or shut down ORA1 instance and restart and then we can set.
SQL> alter system set streams_pool_size=100M sid='ORA3';
System altered.
Similarly set it on rest of the instances ORA2 and ORA1 for example.
SQL> alter system set streams_pool_size=100M sid='ORA2';
System altered.
SQL> alter system set streams_pool_size=100M sid='ORA1';
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool
So we see ORA1 instance is causing the problem. This is because there is insufficient memory in ORA1 instance so you might try to increase SGA_TARGET/MEMORY_TARGET parameter or shut down ORA1 instance and restart and then we can set.
Posted by Arju at 10:05 PM 0
comments Labels: initialization
parameter,Troubleshooting
Labels: initialization
parameter,Troubleshooting|
Reactions:
|
Sunday, October 17, 2010
problem Description
While starting an oracle instance using oradim command or whenever you invoke oradim it fails with DIM-04503 message like below.
E:\xampp\mysql\bin>oradim -start -sid orcl
DIM-04503: Message 4503 not found; product=RDBMS; facility=ORADIM
Cause of the Problem
The problem happened due to incorrect setting of ORACLE_HOME environmental variable. This can also happen if you don't set ORACLE_HOME environmental variable on your windows environment.
Solution of the Problem
Set ORACLE_HOME environmental variable to the location where you have installed oracle software. For example,
E:\xampp\mysql\bin>set ORACLE_HOME=E:\oracle\product\10.2.0\db_2
And now startup database using oradim.
E:\xampp\mysql\bin>oradim -start -sid orcl
Note that, if you have correct ORACLE_HOME setting then invoking oradim will show following output.
While starting an oracle instance using oradim command or whenever you invoke oradim it fails with DIM-04503 message like below.
E:\xampp\mysql\bin>oradim -start -sid orcl
DIM-04503: Message 4503 not found; product=RDBMS; facility=ORADIM
Cause of the Problem
The problem happened due to incorrect setting of ORACLE_HOME environmental variable. This can also happen if you don't set ORACLE_HOME environmental variable on your windows environment.
Solution of the Problem
Set ORACLE_HOME environmental variable to the location where you have installed oracle software. For example,
E:\xampp\mysql\bin>set ORACLE_HOME=E:\oracle\product\10.2.0\db_2
And now startup database using oradim.
E:\xampp\mysql\bin>oradim -start -sid orcl
Note that, if you have correct ORACLE_HOME setting then invoking oradim will show following output.
C:\Documents and Settings\User>oradim
ORADIM: [options]. Refer to manual.
Enter one of the following command:
Create an instance by specifying the following options:
-NEW -SID sid | -SRVC
srvc | -ASMSID sid | -ASMSRVC srvc [-SYSPWD pass]
[-STARTMODE auto|manual]
[-SRVCSTART system|demand] [-PFILE file | -SPFILE]
[-SHUTMODE
normal|immediate|abort] [-TIMEOUT secs] [-RUNAS osusr/ospass]
Edit an instance by specifying the following options:
-EDIT -SID sid |
-ASMSID sid [-SYSPWD pass]
[-STARTMODE auto|manual]
[-SRVCSTART system|demand] [-PFILE file | -SPFILE]
[-SHUTMODE
normal|immediate|abort] [-SHUTTYPE srvc|inst] [-RUNAS osusr/ospass]
Delete instances by specifying the following options:
-DELETE -SID sid |
-ASMSID sid | -SRVC srvc | -ASMSRVC srvc
Startup services and instance by specifying the following options:
-STARTUP -SID sid |
-ASMSID sid [-SYSPWD pass]
[-STARTTYPE
srvc|inst|srvc,inst] [-PFILE filename | -SPFILE]
Shutdown service and instance by specifying the following options:
-SHUTDOWN -SID sid |
-ASMSID sid [-SYSPWD pass]
[-SHUTTYPE
srvc|inst|srvc,inst] [-SHUTMODE normal|immediate|abort]
Query for help by
specifying the following parameters: -? | -h | -help
Note that if you set environmental variable using set command from command prompt then it will be set only for that session. If you want to set as permanent basis then,
- Right click on My Computer icon.
- Click on Properties.
- Go to Advanced tab.
- Click on Environmental Variable.
- Now you can set environmental variable permanently for a user or for system.
- Click on New either for a particular user or for system. If you set for system it will affect all users of the machine.
- Two textbox will appear. In the Variable Name field type ORACLE_HOME and in the Variable Value type the path of your oracle home.
- Click ok and you are done.
Posted by Arju at 6:41 AM 0
comments Labels: Troubleshooting, Windows
Labels: Troubleshooting, Windows|
Reactions:
|
Sunday, September 26, 2010
Problem Description
While mounting oracle database it fails with ORA-12709 like below.
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-12709: error while loading create database character set
Cause of the Problem
The error ORA-12709 is returned due to incorrect setting of environmental variable NLS_LANG
or ORA_NLS33.
Solution of the Problem
Check your NLS_LANG environmental variable by,
$echo $NLS_LANG.
If it is set to wrong value and your database character set is WE8ISO8859P1 then set by,
$export NLS_LANG=American_America.WE8ISO8859P1
Note that, For Oracle7 V7.3.2 version ORA_NLS33 environmental parameter is called ORA_NLS,
for Oracle7 V7.3.3 and V7.3.4 it is called ORA_NLS32,
for Oracle8 it is called ORA_NLS33 because of NLS libraries version.
When using both Oracle8 V8.x and Developer/2000 V1.6.1 in the same Oracle Home, ORA_NLS33 needs to be set to $ORACLE_HOME/ocommon/nls/admin/datad2k
The environmental variable along with database version is given below.
RDBMS 7.2.x -> ORA_NLS
RDBMS 7.3.x -> ORA_NLS32
RDBMS 8.0.x -> ORA_NLS33
RDBMS 8.1.x -> ORA_NLS33
RDBMS 9.X.X -> ORA_NLS33
RDBMS 10.X -> ORA_NLS10
After you set both parameters correctly login as sys user.
$ sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0.8.0 - Production on Sun Sep 26 00:40:18 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Shutdown and Startup the database.
SQL> startup force;
ORACLE instance started.
Total System Global Area 320300808 bytes
Fixed Size 734984 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes
Database mounted.
Database opened.
SQL>
While mounting oracle database it fails with ORA-12709 like below.
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-12709: error while loading create database character set
Cause of the Problem
The error ORA-12709 is returned due to incorrect setting of environmental variable NLS_LANG
or ORA_NLS33.
Solution of the Problem
Check your NLS_LANG environmental variable by,
$echo $NLS_LANG.
If it is set to wrong value and your database character set is WE8ISO8859P1 then set by,
$export NLS_LANG=American_America.WE8ISO8859P1
Note that, For Oracle7 V7.3.2 version ORA_NLS33 environmental parameter is called ORA_NLS,
for Oracle7 V7.3.3 and V7.3.4 it is called ORA_NLS32,
for Oracle8 it is called ORA_NLS33 because of NLS libraries version.
When using both Oracle8 V8.x and Developer/2000 V1.6.1 in the same Oracle Home, ORA_NLS33 needs to be set to $ORACLE_HOME/ocommon/nls/admin/datad2k
The environmental variable along with database version is given below.
RDBMS 7.2.x -> ORA_NLS
RDBMS 7.3.x -> ORA_NLS32
RDBMS 8.0.x -> ORA_NLS33
RDBMS 8.1.x -> ORA_NLS33
RDBMS 9.X.X -> ORA_NLS33
RDBMS 10.X -> ORA_NLS10
After you set both parameters correctly login as sys user.
$ sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0.8.0 - Production on Sun Sep 26 00:40:18 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Shutdown and Startup the database.
SQL> startup force;
ORACLE instance started.
Total System Global Area 320300808 bytes
Fixed Size 734984 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes
Database mounted.
Database opened.
SQL>
Posted by Arju at 10:13 AM 0
comments Labels: Startup
Problem, Troubleshooting
Labels: Startup
Problem, Troubleshooting|
Reactions:
|
Tuesday, August 31, 2010
Problem Description
While executing dbms_workload_repository package the following error message is reported.
While executing dbms_workload_repository package the following error message is reported.
sql> exec
dbms_workload_repository.modify_snapshot_settings(interval=>0);
BEGIN
dbms_workload_repository.modify_snapshot_settings(interval=>0); END;
ERROR at line 1:
ORA-13516: AWR Operation failed: SWRF Schema not initialized
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 85
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 133
ORA-06512: at line 1
Cause of the Problem
These errors have happened because of wrong or invalid objects with respect to AWR.
Solution of the Problem
In order to resolve the issue you need to drop and recreate the AWR objects, which you can do by running CATNOAWR.SQL and CATAWR.SQL scripts.
Note that from 10.2 onwards, the script name has changed. The catalog script for AWR Tables, used to create the Workload Repository Schema is CATAWRTB.SQL .
In case of Oracle 10gR1,
To re-create the AWR objects, by running the catnoawr.sql and catawr.sql scripts located in $ORACLE_HOME/rdbms/admin.
SQL> @$ORACLE_HOME/rdbms/admin/catnoawr.sql
SQL> @$ORACLE_HOME/rdbms/admin/catawr.sql
SQL> shut immediate
SQL> startup
In case of Oracle 10gR2,
SQL> @$ORACLE_HOME/rdbms/admin/CATAWRTB.sql
SQL> shut immediate;
SQL> startup
On re-start of the database instance, the AWR tables will be
populated with the required data.
Note that, the CATNOAWR.SQL scripts is no longer available in 10.2 and cannot be found
in the $ORACLE_HOME/rdbms/admin directory.
Note that, the CATNOAWR.SQL scripts is no longer available in 10.2 and cannot be found
in the $ORACLE_HOME/rdbms/admin directory.
Posted by Arju at 9:03 PM 0
comments Labels: Administration, Troubleshooting
Labels: Administration, Troubleshooting|
Reactions:
|
Monday, August 2, 2010
Problem Description
Connecting as sysdba fails with ORA-28056.
$ sqlplus / as sysdba
ORA-28056: Writing audit records to Windows Event Log failed.
Cause of the Problem
The problem happened because Event Viewer log is full and not able to log anymore events.
Solution of the Problem
You need to free an event log.
- Log in as a administrator or a member of the Administrators group in order to free an event log.
- Open Event Viewer. To do so click on Start Menu, click Control Panel, click Performance and Maintenance, click Administrative Tools, and then double-click Event Viewer.
Or, click on Start Menu, click Control Panel, click Administrative Tools, and then double-click Event Viewer.
- Now you can follow any of the following steps.
1) When a log is full, it stops recording new events. Clearing the log is one way to free the log and start recording new events. To do so, on the Action menu (left click)or on the left side Application/System/Security (as available) right click and select Clear All events.
2) You can also free a log and start recording new events by overwriting old events. To overwrite events, on the Action menu, or on the left side Application/System/Security (as available) click Properties, and then click Overwrite events as needed. This ensures that all new events are written to the log, even when the log is full.
3) You can also start logging new events by increasing the maximum log size. To increase the log size, on the Action menu or on the left side Application/System/Security (as available) click Properties, and then increase the Maximum log size by typing a bigger value.
Connecting as sysdba fails with ORA-28056.
$ sqlplus / as sysdba
ORA-28056: Writing audit records to Windows Event Log failed.
Cause of the Problem
The problem happened because Event Viewer log is full and not able to log anymore events.
Solution of the Problem
You need to free an event log.
- Log in as a administrator or a member of the Administrators group in order to free an event log.
- Open Event Viewer. To do so click on Start Menu, click Control Panel, click Performance and Maintenance, click Administrative Tools, and then double-click Event Viewer.
Or, click on Start Menu, click Control Panel, click Administrative Tools, and then double-click Event Viewer.
- Now you can follow any of the following steps.
1) When a log is full, it stops recording new events. Clearing the log is one way to free the log and start recording new events. To do so, on the Action menu (left click)or on the left side Application/System/Security (as available) right click and select Clear All events.
2) You can also free a log and start recording new events by overwriting old events. To overwrite events, on the Action menu, or on the left side Application/System/Security (as available) click Properties, and then click Overwrite events as needed. This ensures that all new events are written to the log, even when the log is full.
3) You can also start logging new events by increasing the maximum log size. To increase the log size, on the Action menu or on the left side Application/System/Security (as available) click Properties, and then increase the Maximum log size by typing a bigger value.
Posted by Arju at 9:25 PM 0
comments Labels: Audit, Troubleshooting
Labels: Audit, Troubleshooting|
Reactions:
|
Sunday, August 1, 2010
Problem Description
In the sqlnet.ora file there is an entry SQLNET.AUTHENTICATION_SERVICES=NONE and now whenever you connect to database using RMAN it fails with following error.
ORA-21561: OID generation failed
Whenever you change the sqlnet.ora entry to SQLNET.AUTHENTICATION_SERVICES=NTS, it fails with error ORA-12640: Authentication adapter initialization failed.
Cause of the Problem
The problem happened due to lower value of Windows SharedSection heap size.
Solution of the Problem
1. Click on start menu on your windows machine and then go to Run.
2. Type regedit in the Run box and press Enter.
3. In the Registry Editor window navigate to
\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\SubSystems\
You will see key name Windows.
4. Right click on windows name and select modify. You should see value data field containing an entry like below.
%SystemRoot%\system32\csrss.exe ObjectDirectory=\Windows SharedSection=1024,3072,512 Windows=On SubSystemType=Windows ServerDll=basesrv,1 ServerDll=winsrv:UserServerDllInitialization,3 ServerDll=winsrv:ConServerDllInitialization,2 ProfileControl=Off MaxRequestThreads=16
From the field value data the parameter SharedSection's third value (512) is the size of the desktop heap for each desktop that is associated with a "noninteractive" window station.
Increase the desktop heap size that is modify the third value to 1024, so the values are now listed as 1024, 3072, 1024.
After you modify the entry try to connect to database using RMAN and it should resolve the problem.
In the sqlnet.ora file there is an entry SQLNET.AUTHENTICATION_SERVICES=NONE and now whenever you connect to database using RMAN it fails with following error.
ORA-21561: OID generation failed
Whenever you change the sqlnet.ora entry to SQLNET.AUTHENTICATION_SERVICES=NTS, it fails with error ORA-12640: Authentication adapter initialization failed.
Cause of the Problem
The problem happened due to lower value of Windows SharedSection heap size.
Solution of the Problem
1. Click on start menu on your windows machine and then go to Run.
2. Type regedit in the Run box and press Enter.
3. In the Registry Editor window navigate to
\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\SubSystems\
You will see key name Windows.
4. Right click on windows name and select modify. You should see value data field containing an entry like below.
%SystemRoot%\system32\csrss.exe ObjectDirectory=\Windows SharedSection=1024,3072,512 Windows=On SubSystemType=Windows ServerDll=basesrv,1 ServerDll=winsrv:UserServerDllInitialization,3 ServerDll=winsrv:ConServerDllInitialization,2 ProfileControl=Off MaxRequestThreads=16
From the field value data the parameter SharedSection's third value (512) is the size of the desktop heap for each desktop that is associated with a "noninteractive" window station.
Increase the desktop heap size that is modify the third value to 1024, so the values are now listed as 1024, 3072, 1024.
After you modify the entry try to connect to database using RMAN and it should resolve the problem.
Posted by Arju at 8:23 AM 0
comments Labels: Troubleshooting
Labels: Troubleshooting|
Reactions:
|
In the post Troubleshoot
ORA-12547: TNS: lost contact it is already
discussed about the specific causes and solutions of ORA-12547: TNS: lost
contact. If you don't have those specific scenarios then this post will provide
you general solutions for error ORA-12547 which might help you to avoid this
error.
1. Verify that environmental variable ORACLE_HOME, ORACLE_SID, PATH, LD_LIBRARY_PATH are correct.
$ echo $ORACLE_HOME
$ echo $ORACLE_SID
$ echo $LD_LIBRARY_PATH
$ echo $PATH
2. On AIX, HP-UX, Linux, MacOSX, Solaris, Tru64 system make sure kernel parameters settings are ok. To set, check and modify your kernel parameters have a look at the post How to check and modify Kernel Parameters in Linux
and Install Oracle on Linux.
3. There may have incorrect permissions on the ORACLE.exe
The 'ls' command should show permissions 6751 (as follows)
$ cd $ORACLE_HOME/bin
$ ls -l oracle
The output should be
-rwsr-s--x 1 oracle dba
If not then please execute the following
$ chmod 6751 oracle
4. Check whether current ulimit settings is ok.
$ ulimit -a
A sample output is,
1. Verify that environmental variable ORACLE_HOME, ORACLE_SID, PATH, LD_LIBRARY_PATH are correct.
$ echo $ORACLE_HOME
$ echo $ORACLE_SID
$ echo $LD_LIBRARY_PATH
$ echo $PATH
2. On AIX, HP-UX, Linux, MacOSX, Solaris, Tru64 system make sure kernel parameters settings are ok. To set, check and modify your kernel parameters have a look at the post How to check and modify Kernel Parameters in Linux
and Install Oracle on Linux.
3. There may have incorrect permissions on the ORACLE.exe
The 'ls' command should show permissions 6751 (as follows)
$ cd $ORACLE_HOME/bin
$ ls -l oracle
The output should be
-rwsr-s--x 1 oracle dba
If not then please execute the following
$ chmod 6751 oracle
4. Check whether current ulimit settings is ok.
$ ulimit -a
A sample output is,
$ ulimit -a
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
open files (-n) 256
pipe size (512 bytes, -p) 10
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 29995
virtual memory (kbytes, -v) unlimited
If open files (-n) is set to lower then make it unlimited or set
it to a higher value, for example:
$ulimit -n 4096
5. Check the permission on the library files under $ORACLE_HOME/lib and $ORACLE_HOME/lib32 are ok. Since since sqlplus and other binaries require these libraries so if sqlplus does not have permission on these file it may throw ORA-12517: TNS: lost contact. It should have 755 on unix system.
You can give 755 permissions to $ORACLE_HOME/lib and $ORACLE_HOME/lib32
$ chmod -R 755 $ORACLE_HOME/lib
$ chmod -R 755 $ORACLE_HOME/lib32
$ cd $ORACLE_HOME/bin
$ relink all
$ulimit -n 4096
5. Check the permission on the library files under $ORACLE_HOME/lib and $ORACLE_HOME/lib32 are ok. Since since sqlplus and other binaries require these libraries so if sqlplus does not have permission on these file it may throw ORA-12517: TNS: lost contact. It should have 755 on unix system.
You can give 755 permissions to $ORACLE_HOME/lib and $ORACLE_HOME/lib32
$ chmod -R 755 $ORACLE_HOME/lib
$ chmod -R 755 $ORACLE_HOME/lib32
$ cd $ORACLE_HOME/bin
$ relink all
Posted by Arju at 12:09 AM 0
comments Labels: TNS
Error, Troubleshooting
Labels: TNS
Error, Troubleshooting|
Reactions:
|
Saturday, July 31, 2010
Problem Description
Whenever you connect to database as sysdba it fails with ORA-12517 like below.
Whenever you connect to database as sysdba it fails with ORA-12517 like below.
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Sun Aug 01 11:27:27
2010
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
ERROR:
ORA-12547: TNS:lost contact
Enter user-name:
Cause of the Problem
ORA-12547 indicates that the communication channel has been broken. Based on the scenarios cause and solution of the problem is discussed below.
1)Local connection as sysdba (Bequeath):
Problem
BEQ connection fails when connecting with / as sysdba
Cause
Oracle binaries have not been linked correctly
Solution
Relink the Oracle binaries by executing the following command,
$ ORACLE_HOME/bin relink all
2)Bequeath connections fail with ora-12547 and Remote connections fail with ORA-12500:
From the listener.log file it shows,
ORA-12547 indicates that the communication channel has been broken. Based on the scenarios cause and solution of the problem is discussed below.
1)Local connection as sysdba (Bequeath):
Problem
BEQ connection fails when connecting with / as sysdba
Cause
Oracle binaries have not been linked correctly
Solution
Relink the Oracle binaries by executing the following command,
$ ORACLE_HOME/bin relink all
2)Bequeath connections fail with ora-12547 and Remote connections fail with ORA-12500:
From the listener.log file it shows,
TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
The version of the database server is 9.2.0.2
Cause
This is oracle bug 2654576 and happened due to the processes value reaches a limit.
Solution
i) The bug is fixed in patch set 9.2.0.3 and in 10g. So upgrade oracle database is a solution.
ii) Increase the PROCESSES parameter in the init.ora to a higher value for example 2000.
iii) If increasing the PROCESSES parameter does not help then increase the value of the hidden parameter.
_attach_count_slack = 2000
3) Remote connections to the database server fail with ORA-12547
Cause 1
SQLNET.INBOUND_CONNECT_TIMEOUT and/or INBOUND_CONNECT_TIMEOUT_listener_name is set in the database server's sqlnet.ora and listener.ora. If the client fails to establish a connection and complete authentication in the time specified defined by these parameter, then the database server terminates the connection.
If this is the case and your oracle database version is 10g and higher then ORA-3136 errors is reported in the alert.log.
Cause 2
TCP.VALIDNODE_CHECKING is active on the database server and the TCP.INVITED_NODES list does not have the IP address of the failing client.
Alternatively the TCP.EXCLUDED_NODES list contain the IP address of the failing client.
Solution 1
If problem happened due to cause 1 then SQLNET.INBOUND_CONNECT_TIMEOUT and/or INBOUND_CONNECT_TIMEOUT_listener_name to appropriate values.
Note that the database server and the listener has to be restarted for these parameters to take effect.
Solution 2
For cause 2 either add the IP address of the failing client in the TCP.INVITED_NODES list or remove it from the TCP.EXCLUDED_NODES list.
It is recommended to restart the database server and the listener for these parameters to take effect.
4) Listener fails to start with Oracle error ORA-12547
Cause
TCP.VALIDNODE_CHECKING is active on the database server and the TCP.INVITED_NODES list does not have the IP address of the failing client.
Alternatively the TCP.EXCLUDED_NODES list contain the IP address of the failing client.
Solution
Either add the IP address of the failing client in the TCP.INVITED_NODES list or remove it from the TCP.EXCLUDED_NODES list.
It is recommended to restart the database server and the listener for these parameters to take effect.
5) After changing /etc/system and rebooting system, the 10g R2 TNS Listener fails to start
Cause
This is oracle bug 2654576 and happened due to the processes value reaches a limit.
Solution
i) The bug is fixed in patch set 9.2.0.3 and in 10g. So upgrade oracle database is a solution.
ii) Increase the PROCESSES parameter in the init.ora to a higher value for example 2000.
iii) If increasing the PROCESSES parameter does not help then increase the value of the hidden parameter.
_attach_count_slack = 2000
3) Remote connections to the database server fail with ORA-12547
Cause 1
SQLNET.INBOUND_CONNECT_TIMEOUT and/or INBOUND_CONNECT_TIMEOUT_listener_name is set in the database server's sqlnet.ora and listener.ora. If the client fails to establish a connection and complete authentication in the time specified defined by these parameter, then the database server terminates the connection.
If this is the case and your oracle database version is 10g and higher then ORA-3136 errors is reported in the alert.log.
Cause 2
TCP.VALIDNODE_CHECKING is active on the database server and the TCP.INVITED_NODES list does not have the IP address of the failing client.
Alternatively the TCP.EXCLUDED_NODES list contain the IP address of the failing client.
Solution 1
If problem happened due to cause 1 then SQLNET.INBOUND_CONNECT_TIMEOUT and/or INBOUND_CONNECT_TIMEOUT_listener_name to appropriate values.
Note that the database server and the listener has to be restarted for these parameters to take effect.
Solution 2
For cause 2 either add the IP address of the failing client in the TCP.INVITED_NODES list or remove it from the TCP.EXCLUDED_NODES list.
It is recommended to restart the database server and the listener for these parameters to take effect.
4) Listener fails to start with Oracle error ORA-12547
Cause
TCP.VALIDNODE_CHECKING is active on the database server and the TCP.INVITED_NODES list does not have the IP address of the failing client.
Alternatively the TCP.EXCLUDED_NODES list contain the IP address of the failing client.
Solution
Either add the IP address of the failing client in the TCP.INVITED_NODES list or remove it from the TCP.EXCLUDED_NODES list.
It is recommended to restart the database server and the listener for these parameters to take effect.
5) After changing /etc/system and rebooting system, the 10g R2 TNS Listener fails to start
$ lsnrctl start
Starting /u01/oracle/product/10.2.0/Db_1/bin/tnslsnr: please
wait...
TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
System parameter file is
/u01/oracle/product/10.2.0/Db_1/network/admin/listener.ora
Log messages written to
/u01/oracle/product/10.2.0/Db_1/network/log/listener.log
Trace information written to
/u01/oracle/product/10.2.0/Db_1/network/trace/listener.trc
Listening on:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server11)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc10)))
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server11)(PORT=1521)))
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Solaris Error: 131: Connection reset by peer
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=extproc10)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Solaris Error: 146: Connection refused
Cause
This one is happened due to a DNS client process, 'BIND 9' not running/started on the Solaris 10 Server.
Solution
You need to start the BIND 9 DNS Client on the Solaris10 Server.
This one is happened due to a DNS client process, 'BIND 9' not running/started on the Solaris 10 Server.
Solution
You need to start the BIND 9 DNS Client on the Solaris10 Server.
Posted by Arju at 11:00 PM 1
comments Labels: Listener, TNS
Error,Troubleshooting
Labels: Listener, TNS
Error,Troubleshooting|
Reactions:
|
Saturday, February 27, 2010
Problem Description
Oracle full database export fails with error stack EXP-00008, ORA-04063, ORA-06508, EXP-00083 like below.
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling WMSYS.LT_EXPORT_PKG.schema_info_exp
Cause of the Problem
The above problems are caused by the revoke of the execute privilege on UTL_FILE package from Public.
Oracle Workspace Manager (OWM) and some other database components (which are not installed in the database) need the privilege to execute procedure SYS.UTL_FILE.
During the creation of a default Database, the Workspace Manager gets installed by default. and the WMSYS user becomes intertwined with the export process and must be valid or at least the major parts for an export to complete properly as the export process looks for any "version-enabled" tables that Workspace Manager schema may be using.
Solution of the Problem
In order to solve above problems do following steps.
1. Grant execute privilege on SYS.UTL_FILE package to WMSYS user.
SQL> grant execute on SYS.UTL_FILE to WMSYS;
2. check for invalid objects in the WMSYS schema using following query,
SQL> select object_name,object_type,owner,status from dba_objects where status='INVALID' and owner='WMSYS';
3. Run script $ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
Related Documents
http://arjudba.blogspot.com/2010/02/exp-00008-ora-06550-pls-00201-exp-00083.html
http://arjudba.blogspot.com/2010/02/ora-39127-ora-04063-ora-06508-ora-06512.html
http://arjudba.blogspot.com/2010/02/exp-00008-ora-04063-ora-06508-exp-00083.html
http://arjudba.blogspot.com/2009/12/export-fully-fails-with-pls-00201-ora.html
http://arjudba.blogspot.com/2009/12/export-fails-with-exp-00002-error-in.html
http://arjudba.blogspot.com/2009/01/ora-31655-no-data-or-metadata-objects.html
http://arjudba.blogspot.com/2009/01/expdp-fails-with-ora-31693-ora-06502.html
http://arjudba.blogspot.com/2008/12/ora-39095-dump-file-space-has-been.html
http://arjudba.blogspot.com/2008/09/expdp-fails-with-ora-31626-ora-31633.html
http://arjudba.blogspot.com/2008/07/data-pump-export-fails-with-ora-39000.html
http://arjudba.blogspot.com/2009/07/ora-39165-schema-sys-was-not-found-ora.html
http://arjudba.blogspot.com/2009/07/ora-39166-object-was-not-found-sys.html
http://arjudba.blogspot.com/2009/05/ora-39000-ora-39143-dump-file-may-be.html
http://arjudba.blogspot.com/2009/05/expdp-fails-with-ora-39001ora-39169ora.html
Oracle full database export fails with error stack EXP-00008, ORA-04063, ORA-06508, EXP-00083 like below.
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "WMSYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LT_EXPORT_PKG"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling WMSYS.LT_EXPORT_PKG.schema_info_exp
Cause of the Problem
The above problems are caused by the revoke of the execute privilege on UTL_FILE package from Public.
Oracle Workspace Manager (OWM) and some other database components (which are not installed in the database) need the privilege to execute procedure SYS.UTL_FILE.
During the creation of a default Database, the Workspace Manager gets installed by default. and the WMSYS user becomes intertwined with the export process and must be valid or at least the major parts for an export to complete properly as the export process looks for any "version-enabled" tables that Workspace Manager schema may be using.
Solution of the Problem
In order to solve above problems do following steps.
1. Grant execute privilege on SYS.UTL_FILE package to WMSYS user.
SQL> grant execute on SYS.UTL_FILE to WMSYS;
2. check for invalid objects in the WMSYS schema using following query,
SQL> select object_name,object_type,owner,status from dba_objects where status='INVALID' and owner='WMSYS';
3. Run script $ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
Related Documents
http://arjudba.blogspot.com/2010/02/exp-00008-ora-06550-pls-00201-exp-00083.html
http://arjudba.blogspot.com/2010/02/ora-39127-ora-04063-ora-06508-ora-06512.html
http://arjudba.blogspot.com/2010/02/exp-00008-ora-04063-ora-06508-exp-00083.html
http://arjudba.blogspot.com/2009/12/export-fully-fails-with-pls-00201-ora.html
http://arjudba.blogspot.com/2009/12/export-fails-with-exp-00002-error-in.html
http://arjudba.blogspot.com/2009/01/ora-31655-no-data-or-metadata-objects.html
http://arjudba.blogspot.com/2009/01/expdp-fails-with-ora-31693-ora-06502.html
http://arjudba.blogspot.com/2008/12/ora-39095-dump-file-space-has-been.html
http://arjudba.blogspot.com/2008/09/expdp-fails-with-ora-31626-ora-31633.html
http://arjudba.blogspot.com/2008/07/data-pump-export-fails-with-ora-39000.html
http://arjudba.blogspot.com/2009/07/ora-39165-schema-sys-was-not-found-ora.html
http://arjudba.blogspot.com/2009/07/ora-39166-object-was-not-found-sys.html
http://arjudba.blogspot.com/2009/05/ora-39000-ora-39143-dump-file-may-be.html
http://arjudba.blogspot.com/2009/05/expdp-fails-with-ora-39001ora-39169ora.html
Posted by Arju at 2:02 AM 0
comments Labels: Data
Pump, Export,Troubleshooting
Labels: Data
Pump, Export,Troubleshooting|
Reactions:
|
Wednesday, February 17, 2010
Problem Description
RMAN backup fails with ORA-00204 ORA-00202 ORA-27071 HP-UX Error: 9: Bad file number as below.
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 29 00:01:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-00204: error in reading (block 1, # blocks 1) of control file
ORA-00202: control file: '/SID/oradata/APEXP/control01.ctl'
ORA-27071: unable to seek to desired position in file
HP-UX Error: 9: Bad file number
An another version of error message appear while starting RMAN is,
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database:
ORA-00204: error in reading (block 3, # blocks 1) of control file
ORA-00202: control file: '/SID/oradata/APEXP/control01.ctl'
ORA-27071: unable to seek to desired position in file
HP-UX Error: 9: Bad file number Additional information: 2
Cause of the Problem
The RMAN-06900, RMAN-06901 error occurs when the RMAN couldn't log the output in $rman_output. The actual investigation of the error comes in the associated additional error messages. If you look for additional error message we will see ORA-27071, HP-UX Error: 9: Bad file number. The word 'HP-UX Error: 9: Bad file number' indicates that error is coming from operating system. The OS user who is invoking RMAN don't have OS permission on the control file and hence HP-UX generates bad file number error.
Solution of the Problem
The user who is invoking RMAN don't have write permission on the controlfile. You must need to change permission from operating system. You can do so by issuing,
$chmod 666 /SID/oradata/APEXP/control01.ctl
After you change the permission now try to invoke RMAN command again.
Related Documents:
RMAN backup fails with ORA-00204 ORA-00202 ORA-27071 HP-UX Error: 9: Bad file number as below.
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 29 00:01:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-00204: error in reading (block 1, # blocks 1) of control file
ORA-00202: control file: '/SID/oradata/APEXP/control01.ctl'
ORA-27071: unable to seek to desired position in file
HP-UX Error: 9: Bad file number
An another version of error message appear while starting RMAN is,
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database:
ORA-00204: error in reading (block 3, # blocks 1) of control file
ORA-00202: control file: '/SID/oradata/APEXP/control01.ctl'
ORA-27071: unable to seek to desired position in file
HP-UX Error: 9: Bad file number Additional information: 2
Cause of the Problem
The RMAN-06900, RMAN-06901 error occurs when the RMAN couldn't log the output in $rman_output. The actual investigation of the error comes in the associated additional error messages. If you look for additional error message we will see ORA-27071, HP-UX Error: 9: Bad file number. The word 'HP-UX Error: 9: Bad file number' indicates that error is coming from operating system. The OS user who is invoking RMAN don't have OS permission on the control file and hence HP-UX generates bad file number error.
Solution of the Problem
The user who is invoking RMAN don't have write permission on the controlfile. You must need to change permission from operating system. You can do so by issuing,
$chmod 666 /SID/oradata/APEXP/control01.ctl
After you change the permission now try to invoke RMAN command again.
Related Documents:
Posted by Arju at 12:25 AM 0
comments Labels: RMAN, Troubleshooting
Labels: RMAN, Troubleshooting|
Reactions:
|
Saturday, January 30, 2010
Problem Description
RMAN backups has been successfully completed but from the backup logs the following error have been generated.
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 29 00:01:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 64 rows exceeded
A variation of the above error is,
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 29 00:01:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 128 rows exceeded
Analysis And Solution of the Problem
As soon as you get error message RMAN-06900 and RMAN-06901 you immediately look for associated error messages. The associated error message should tell you more information and you need to look for those messages in order to solve this error. For example here we are getting additional error "ORA-19921: maximum number of 128 rows exceeded". So our solution will lie on ORA-19921.
RMAN gives a warning message of RMAN-6900, RMAN-6901 ORA-19921 when the output is too huge and oracle is not able to write the log output into v$rman_output. There are several reasons when oracle will not be able to write the log output into V$RMAN_OUTPUT. For example, one of your control file becomes corrupted, hence oracle is unable to write the the log output to RMAN view/table. The another major reasons cause this problem to happen is due to oracle bug.
You get oracle error "ORA-19921: maximum number of 64 rows exceeded" due to oracle Bug 465973.
You get oracle error "ORA-19921: MAXIMUM NUMBER OF 128 ROWS EXCEEDED" due to oracle Bug 8264365.
The bug 465973 is fixed in Oracle 10.2.0.4 and Oracle 11G. So if you see oracle only gives warning message and backup successfully done then you can simply ignore error messages or upgrade oracle or apply patch where bug is fixed.
However if you see your backup is not done due to these errors then immediately check for additional messages. If it happened due to controlfiles then take care of those errors.
Related Documents:
RMAN backups has been successfully completed but from the backup logs the following error have been generated.
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 29 00:01:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 64 rows exceeded
A variation of the above error is,
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 29 00:01:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 128 rows exceeded
Analysis And Solution of the Problem
As soon as you get error message RMAN-06900 and RMAN-06901 you immediately look for associated error messages. The associated error message should tell you more information and you need to look for those messages in order to solve this error. For example here we are getting additional error "ORA-19921: maximum number of 128 rows exceeded". So our solution will lie on ORA-19921.
RMAN gives a warning message of RMAN-6900, RMAN-6901 ORA-19921 when the output is too huge and oracle is not able to write the log output into v$rman_output. There are several reasons when oracle will not be able to write the log output into V$RMAN_OUTPUT. For example, one of your control file becomes corrupted, hence oracle is unable to write the the log output to RMAN view/table. The another major reasons cause this problem to happen is due to oracle bug.
You get oracle error "ORA-19921: maximum number of 64 rows exceeded" due to oracle Bug 465973.
You get oracle error "ORA-19921: MAXIMUM NUMBER OF 128 ROWS EXCEEDED" due to oracle Bug 8264365.
The bug 465973 is fixed in Oracle 10.2.0.4 and Oracle 11G. So if you see oracle only gives warning message and backup successfully done then you can simply ignore error messages or upgrade oracle or apply patch where bug is fixed.
However if you see your backup is not done due to these errors then immediately check for additional messages. If it happened due to controlfiles then take care of those errors.
Related Documents:
Posted by Arju at 12:56 AM 3
comments Labels: Bug, RMAN, Troubleshooting
Labels: Bug, RMAN, Troubleshooting|
Reactions:
|
Tuesday, January 19, 2010
Problem Description
Shut down oracle database (shutdown normal/shutdown immediate) fails with following errors.
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01090: shutdown in progress - connection is not permitted
Cause of the Problem
The problem happened because background processes are hanging/not stared correctly during the previous startup of this database. Hence the smeaphores and shared memory segements are not getting detached properly now during shutdown.
Solution of the Problem
1) Verify that there are no background processes owned by "oracle"
$ ps -ef | grep ora_ | grep $ORACLE_SID
If background processes exist, remove them by using the Unix "kill" command.
For example to kill a process ID number 1200 issue,
$ kill -9 1200
2) Verify that no shared memory segments and semaphores that are owned by "oracle" still exist.
If there are shared memory segments and semaphores owned by "oracle", remove the shared memory segments and semaphores.
A) checking and removing shared memory.
Verify the shared memory segment by,
$ ipcs -mt
To remove shared memory segment issue,
$ ipcrm -m Shared_Memory_ID_Number
where Shared_Memory_ID_Number must be replace by shared memory id number.
B) checking and removing semaphores
Check the semaphores by,
$ipcs -sbt
To remove the semaphores issue,
$ ipcrm -s Semaphore_ID_Number
where Semaphore_ID_Number must be replaced by your Semaphore ID Number.
3) Verify that file $ORACLE_HOME/dbs/lk{db_name} does not exist where db_name is your actual database name.
$ cd $ORACLE_HOME/dbs
$ rm lk{db_name}
4) Verify that file "$ORACLE_HOME/dbs/sgadef{sid}.dbf" does not exist where sid is your actual database SID.
$ cd $ORACLE_HOME/dbs
$ rm sgadef{SID}.dbf
5) Exit sql*plus window, re login to sql*plus and try to bring up oracle one step at a time.
$ sqlplus /nolog
SQL> startup nomount
SQL> alter database mount;
SQL> alter database open;
Note that while performing above steps it is recommended to first bring down all the other instances running on the server and then perform the above steps one by one. Re-booting the server will also fix this problem.
Shut down oracle database (shutdown normal/shutdown immediate) fails with following errors.
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01090: shutdown in progress - connection is not permitted
Cause of the Problem
The problem happened because background processes are hanging/not stared correctly during the previous startup of this database. Hence the smeaphores and shared memory segements are not getting detached properly now during shutdown.
Solution of the Problem
1) Verify that there are no background processes owned by "oracle"
$ ps -ef | grep ora_ | grep $ORACLE_SID
If background processes exist, remove them by using the Unix "kill" command.
For example to kill a process ID number 1200 issue,
$ kill -9 1200
2) Verify that no shared memory segments and semaphores that are owned by "oracle" still exist.
If there are shared memory segments and semaphores owned by "oracle", remove the shared memory segments and semaphores.
A) checking and removing shared memory.
Verify the shared memory segment by,
$ ipcs -mt
To remove shared memory segment issue,
$ ipcrm -m Shared_Memory_ID_Number
where Shared_Memory_ID_Number must be replace by shared memory id number.
B) checking and removing semaphores
Check the semaphores by,
$ipcs -sbt
To remove the semaphores issue,
$ ipcrm -s Semaphore_ID_Number
where Semaphore_ID_Number must be replaced by your Semaphore ID Number.
3) Verify that file $ORACLE_HOME/dbs/lk{db_name} does not exist where db_name is your actual database name.
$ cd $ORACLE_HOME/dbs
$ rm lk{db_name}
4) Verify that file "$ORACLE_HOME/dbs/sgadef{sid}.dbf" does not exist where sid is your actual database SID.
$ cd $ORACLE_HOME/dbs
$ rm sgadef{SID}.dbf
5) Exit sql*plus window, re login to sql*plus and try to bring up oracle one step at a time.
$ sqlplus /nolog
SQL> startup nomount
SQL> alter database mount;
SQL> alter database open;
Note that while performing above steps it is recommended to first bring down all the other instances running on the server and then perform the above steps one by one. Re-booting the server will also fix this problem.
Problem Description
RMAN backups has been successfully completed but from the backup logs the following error have been generated.
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 29 00:01:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 64 rows exceeded
A variation of the above error is,
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 29 00:01:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 128 rows exceeded
Analysis And Solution of the Problem
As soon as you get error message RMAN-06900 and RMAN-06901 you immediately look for associated error messages. The associated error message should tell you more information and you need to look for those messages in order to solve this error. For example here we are getting additional error "ORA-19921: maximum number of 128 rows exceeded". So our solution will lie on ORA-19921.
RMAN gives a warning message of RMAN-6900, RMAN-6901 ORA-19921 when the output is too huge and oracle is not able to write the log output into v$rman_output. There are several reasons when oracle will not be able to write the log output into V$RMAN_OUTPUT. For example, one of your control file becomes corrupted, hence oracle is unable to write the the log output to RMAN view/table. The another major reasons cause this problem to happen is due to oracle bug.
You get oracle error "ORA-19921: maximum number of 64 rows exceeded" due to oracle Bug 465973.
You get oracle error "ORA-19921: MAXIMUM NUMBER OF 128 ROWS EXCEEDED" due to oracle Bug 8264365.
The bug 465973 is fixed in Oracle 10.2.0.4 and Oracle 11G. So if you see oracle only gives warning message and backup successfully done then you can simply ignore error messages or upgrade oracle or apply patch where bug is fixed.
However if you see your backup is not done due to these errors then immediately check for additional messages. If it happened due to controlfiles then take care of those errors.
Related Documents:
RMAN backups has been successfully completed but from the backup logs the following error have been generated.
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 29 00:01:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 64 rows exceeded
A variation of the above error is,
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 29 00:01:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 128 rows exceeded
Analysis And Solution of the Problem
As soon as you get error message RMAN-06900 and RMAN-06901 you immediately look for associated error messages. The associated error message should tell you more information and you need to look for those messages in order to solve this error. For example here we are getting additional error "ORA-19921: maximum number of 128 rows exceeded". So our solution will lie on ORA-19921.
RMAN gives a warning message of RMAN-6900, RMAN-6901 ORA-19921 when the output is too huge and oracle is not able to write the log output into v$rman_output. There are several reasons when oracle will not be able to write the log output into V$RMAN_OUTPUT. For example, one of your control file becomes corrupted, hence oracle is unable to write the the log output to RMAN view/table. The another major reasons cause this problem to happen is due to oracle bug.
You get oracle error "ORA-19921: maximum number of 64 rows exceeded" due to oracle Bug 465973.
You get oracle error "ORA-19921: MAXIMUM NUMBER OF 128 ROWS EXCEEDED" due to oracle Bug 8264365.
The bug 465973 is fixed in Oracle 10.2.0.4 and Oracle 11G. So if you see oracle only gives warning message and backup successfully done then you can simply ignore error messages or upgrade oracle or apply patch where bug is fixed.
However if you see your backup is not done due to these errors then immediately check for additional messages. If it happened due to controlfiles then take care of those errors.
Related Documents:
No comments:
Post a Comment