Problem Description
While setting any parameter in the physical standby database spfile it fails with ORA-17510. For example, changing LOCAL_LISTENER parameter in spfile fails with following message.
SQL> ALTER SYSTEM SET LOCAL_LISTENER=LISTENER_LOCAL scope=both sid='*';
ORA-17510: Attempt to do i/o beyond file size
If you look for alert log entry you will notice error message like,
Thu Sep 16 17:13:17 2010
Errors in file /u01/app/oracle/diag/rdbms/bdafisdrs/bdafisdc2/trace/bdafisdc2_mmon_12522.trc (incident=14620):
ORA-00600: internal error code, arguments: [kmgs_parameter_update_timeout_1], [17510], [], [], [], [], [], [], [], [], [], []
ORA-17510: Attempt to do i/o beyond file size
Incident details in: /u01/app/oracle/diag/rdbms/bdafisdrs/bdafisdc2/incident/incdir_14620/bdafisdc2_mmon_12522_i14620.trc
Cause of the Problem
The error ORA-17510 and ORA-600 [kmgs_parameter_update_timeout_1] indicates a problem of extending the size of the spfile, or a possible corruption of the spfile. Oracle is not able to write entry in the spfile and hence error returned.
Solution of the Problem
In order to solve the problem you need to recreate the spfile.
1. First create a pfile from the spfile, e.g.:
SQL> connect / as sysdba
SQL> create pfile='/tmp/pfile.ora' from spfile;
2. Startup the instance using the pfile created, e.g.:
SQL> starup pfile='/tmp/pfile.ora';
3. Then recreate the spfile from the pfile, e.g.:
SQL> connect / as sysdba
SQL> create spfile from pfile='/tmp/pfile.ora';
4. Startup the instance with the spfile and modify the parameter.
SQL> startup
SQL> ALTER SYSTEM SET LOCAL_LISTENER=LISTENER_LOCAL scope=both sid='*';
Showing posts with label Spfile. Show all posts
Showing posts with label Spfile. Show all posts
Sunday, September 19, 2010
Saturday, December 5, 2009
OMF, init.ora Parameter files, Startup, Shutdown Exercises
| G E N E R A L O V E R V I E W |  |
| Scenario/Summary |
Oracle provides two primary types of file management; User Managed Files (UMF) and Oracle Managed Files (OMF). As part of this exercise, you will need to supply some information as to how you would use both of these approaches and discuss some of the advantages of each.
Along with creating a database, there are other additional files that must be created or altered. One such file is the INIT.ORA file, or initalization file for the database. The example code in Step 2, although not a complete init file, will need some modifications made to it.
Finally, this exercise will introduce you to the processes and steps of shutting down your database and starting it back up again using the initialization parameter file speific to your database. You will go through this process many times during your labs, starting in Week three, so it is best to get the hang of it now, while we have some time.
For exercise part three, you only need access to your database instance. If you have any difficulties connecting your database instance, notify your instructor as soon as possible.
Now you are ready to proceed.
 | I N D I V I D U A L E X E R C I S E S |  |
| HANDS-ON #1: Working with Dictionary and Oracle Managed files |
- Describe how you plan to rectify the I/O problem with the new Oracle10g database using the UMF method.
- Describe how you would solve the same problem using the OMF method.
| HANDS-ON #2: Configuring the INIT.ORA file |
- List the errors you find.
- Rewrite the code with corrections. Format the code as you would expect to see it in the init.ora file with appropriate section titles in comment areas. You can use the example in the iLab Manual as a guide.
Place and save your answers in the Word document named week2_exercise.doc that you have started for this week's exercises###############
Cache and I/O
###############
db block size=8192, db domain="detroit.usa"
remote login passwordfIle=EXCLUSIVE control_files=("D:\newlogs\contro199.ctl") maxinstances=2
compatible=101020
###############
Database Name
###############
db name=prod901.detroit.usa
instance name=trialO2
| HANDS-ON #3: Manual Shutdown and Startup of the Database |
For this part of the exercise, you are going to shutdown your individual database instance and then open it up in stages, using the SQL*Plus editor. The process you will use is outlined in the iLab Manual, under the section titled "Shutting Down and Opening the Database Instance". You might want to reference the iLab Manual as a guide for this exercise. The following steps will take you through this process. Once you have successfully logged into Oracle through the editor, start a spool session to capture your results. Refer to the iLab Manual for the correct way to set up a SPOOL session in the Citrix environment. The spool file will be what you submit for a grade for this part of the exercise. NOTE: This process is Oracle specific and not version specific. Some of the examples may show a different release of Oracle than the one you are using. This will not affect the outcome of this exercise.
Let's get started.
- You will first need to log into the Oracle lab environment through Citrix. If you have not done this yet, first follow the instructions in the iLab Manual for logging into Citrix. Once you are into Citrix, select the Oracle folder to access the different tools available.
- Select and open the Windows Explorer application from the available icons. Once open then open up the O: drive. Continue to navigate through the folders until you find the folder titled 'Pfile'. Inside this folder is a file named INITOL###.ORA (where ### is your instance number). You want to write down or copy the full path to this file starting with the O: drive letter. Be sure you include the actual file name in your path. You will need this in the steps to follow. Once you hve done this you can close the Windows Explorer.
- Now select the SQL*Plus icon in the Oracle folder and open the SQL Plus editor. Once the application starts you should see a login box.
- Log into your database instance using SYS as the User Name, ORACLE as the password, and DB###.WORLD AS SYSDBA in the Host String box (where ### is your instance number).
- Once connected to Oracle, you should note down your screen.
Next, you need to shut down the database. Type in the command SHUTDOWN IMMEDIATE and then hit ENTER. The database will go through MOUNT and NOMOUNT states as it shuts down and will end by stating "Oracle instance shut down".
Now you want to go through the process of opening the database into each of the various modes. You will be starting up the database and shutting it down several times during this process. NOTE: You are doing this exercise from a mapped network drive in Citrix so the path to your init.ora file will start with the O: drive at \oracle, as in O:\DB####\oracle\admin\pfile\
The following will outline the steps to follow.
Start the database in NOMOUNT --
- Use STARTUP NOMOUNT PFILE= and then place the complete path to your initol###.ora file at the end. Your path should look similar to O:\oracle\admin\pfile\
initDB400.ora, only your instance name will be in the file name in place of DB400. - Once the database is in NOMOUNT, shut the instance back down using SHUTDOWN IMMEDIATE. Do not be alarmed when you see what looks like an error message saying the database is not open.
- Use STARTUP MOUNT PFILE= and then place the complete path to your initol###.ora file at the end. Your path should look similar to O:\Oracle\admin\pfile\
initDB400.ora, only your instance name will be in the file name in place of DB400. - Once the database is in MOUNT, shut the instance back down using SHUTDOWN IMMEDIATE. Do not be alarmed when you see what looks like an error message saying the database is not open.
- Use STARTUP PFILE= and then place the complete path to your initol###.ora file at the end. Remember that the OPEN key word is optional. Your path should look similar to this: O:\Oracle\admin\pfile\
initDB400.ora, only your instance name will be in the file name in place of DB400. - Once the database is in NOMOUNT, shut the instance back down using SHUTDOWN IMMEDIATE. Do not be alarmed when you see what looks like an error message saying the database is not open.
- Use STARTUP NOMOUNT PFILE= and then place the complete path to your initol###.ora file at the end. Your path should look similar to O:\Oracle\admin\pfile\
initDB400.ora, only your instance name will be in the file name in place of DB400. - Once the database is in NOMOUNT, issue an ALTER DATABASE MOUNT command. Remember to end the command with a semicolon.
- Once in MOUNT status (you should get a ?Database altered? reply from the system) then enter the ALTER DATABASE OPEN command.
- Verify that you database is in OPEN status by querying the STATUS column from the V$INSTANCE data dictionary view.
You can now close the SQL Plus editor and exit out of the Desktop. Remember to Log Off the Citrix server by selecting the Log Off button. DO NOT just close the window as this will keep your session open. Remember that the spool file you created is on the C: drive of your computer
Thursday, July 2, 2009
While startup ORA-27046: file size is not a multiple of logical block size
Problem Description
While creating pfile from your spfile you got the following ORA-27046. Though the error message that it shows depends on oracle version.
In Oracle 11g the error message look like,
SQL> create pfile from spfile;
create pfile from spfile
*
ERROR at line 1:
ORA-01565: error in identifying file
'%ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 911)
In Oracle 10g the error message looks like,
SQL> create pfile from spfile;
create pfile from spfile
*
ERROR at line 1:
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 3583)
In Oracle 9.2 the error message looks like,
SQL> create pfile='init.ora' from spfile='/oracle/oradata/product/spfileprod.ora';
create pfile='init.ora' from spfile='/oracle/oradata/product/spfileprod.ora'
*
ERROR at line 1:
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
Additional information: 254
Cause of the Problem
Oracle database finds a spfile in the desired location but it was not able to read the file as the server parameter file was corrupted. If you try to create a pfile from a corrupted spfile then ORA-27046 error occurs. In the same way, if your database spfile with it's default name, found in default location which refer on windows under %ORACLE_HOME%\database, on unix under $ORACLE_HOME/dbs and your spfile is corrupted and then if you issue startup Oracle can report a ORA-1078.
Solution of the problem
It is simple to understand that there is no way the spfile can be repaired or modified manually because spfile is a binary file. To know more about spfile have a look at Pfile and Spfile in Oracle.
So now the solution is to restore a spfile from backup, or recreate a new spfile.
If a SPFILE backup exists, restore it to the original location. The backup could be a copy of the spfile or from a RMAN backup. How you can restore it from RMAN is discussed in Restore SPfile from RMAN and Restore an Spfile from Autobackup.
However if the spfile backup does not exist, one can use an existing pfile or the list of non-default parameters listed in the alert log at startup time, or one can also get the parameters from the corrupted spfile using utilities like strings available on unix, similar utilities can be found for Windows as well. On windows you can simply open the file using notepad ++ or ultra edit and copy readable part of the corrupted spfile and save it as a pfile.
After that using the SQL command CREATE SPFILE FROM PFILE create a spfile from the pfile.
A detail about the procedure is discussed inside solution part of the post
Different ways to discover spfile.
In unix simply,
$ strings spfile$ORACLE_SID.ora init$ORACLE_SID.ora
where $ORACLE_SID will be replaced by your oracle sid.
SQL> conn / as sysdba
SQL> create spfile from pfile ;
Will create a good spfile.
After you create spfile now you are done. You can startup your database as well as you can create as many pfile from spfile as you wish.
Related Documents
While creating pfile from your spfile you got the following ORA-27046. Though the error message that it shows depends on oracle version.
In Oracle 11g the error message look like,
SQL> create pfile from spfile;
create pfile from spfile
*
ERROR at line 1:
ORA-01565: error in identifying file
'%ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 911)
In Oracle 10g the error message looks like,
SQL> create pfile from spfile;
create pfile from spfile
*
ERROR at line 1:
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 3583)
In Oracle 9.2 the error message looks like,
SQL> create pfile='init.ora' from spfile='/oracle/oradata/product/spfileprod.ora';
create pfile='init.ora' from spfile='/oracle/oradata/product/spfileprod.ora'
*
ERROR at line 1:
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
Additional information: 254
Cause of the Problem
Oracle database finds a spfile in the desired location but it was not able to read the file as the server parameter file was corrupted. If you try to create a pfile from a corrupted spfile then ORA-27046 error occurs. In the same way, if your database spfile with it's default name, found in default location which refer on windows under %ORACLE_HOME%\database, on unix under $ORACLE_HOME/dbs and your spfile is corrupted and then if you issue startup Oracle can report a ORA-1078.
Solution of the problem
It is simple to understand that there is no way the spfile can be repaired or modified manually because spfile is a binary file. To know more about spfile have a look at Pfile and Spfile in Oracle.
So now the solution is to restore a spfile from backup, or recreate a new spfile.
If a SPFILE backup exists, restore it to the original location. The backup could be a copy of the spfile or from a RMAN backup. How you can restore it from RMAN is discussed in Restore SPfile from RMAN and Restore an Spfile from Autobackup.
However if the spfile backup does not exist, one can use an existing pfile or the list of non-default parameters listed in the alert log at startup time, or one can also get the parameters from the corrupted spfile using utilities like strings available on unix, similar utilities can be found for Windows as well. On windows you can simply open the file using notepad ++ or ultra edit and copy readable part of the corrupted spfile and save it as a pfile.
After that using the SQL command CREATE SPFILE FROM PFILE create a spfile from the pfile.
A detail about the procedure is discussed inside solution part of the post
Different ways to discover spfile.
In unix simply,
$ strings spfile$ORACLE_SID.ora init$ORACLE_SID.ora
where $ORACLE_SID will be replaced by your oracle sid.
SQL> conn / as sysdba
SQL> create spfile from pfile ;
Will create a good spfile.
After you create spfile now you are done. You can startup your database as well as you can create as many pfile from spfile as you wish.
Related Documents
Tuesday, September 2, 2008
Recover database Issue after missing spfile or pfile
In order to discover pfile or spfile I have discussed in The different ways to discover initialization parameter (spfile or pfile)
Now you may have the scenario that you yet not discovered your spfile or pfile then the remaining option is to create a new pfile. Suppose that you create your spfile like this.
-bash-3.00$ vi /oradata2/1.pfile
undo_tablespace=UNDOTBS1
undo_management=AUTO
compatible=10.2.0.1.0
db_name=arjudba
sga_target=400M
That is without control_files parameter inside it. It is ok if you don't have any controlfile and you want to create a new one. If you don't give any location inside pfile then oracle will create controlfile in the location of $ORACLE_HOME/dbs/cntrlarjudba.dbf.
However if you specify control_files parameter inside pfile then oracle will attempt to create controlfile in the specified location. Like if you create pfile as
-bash-3.00$ vi /oradata2/1.pfile
undo_tablespace=UNDOTBS1
undo_management=AUTO
compatible=10.2.0.1.0
db_name=arjudba
control_files='/oradata2/arjudba/arjudba/control01.ctl','/oradata2/arjudba/arjud
ba/control02.ctl'
sga_target=400M
then create controlfile statement will create controlfile in the location of /oradata2/arjudba/arjudba/control01.ctl, and /oradata2/arjudba/arjudba/control02.ctl
If you don't give any location of control_file parameter inside pfile then further startup will fails with ORA-00205
Like,
SQL> startup
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 2019608 bytes
Variable Size 83889896 bytes
Database Buffers 117440512 bytes
Redo Buffers 6365184 bytes
ORA-00205: error in identifying control file, check alert log for more info
Now you may have the scenario that you yet not discovered your spfile or pfile then the remaining option is to create a new pfile. Suppose that you create your spfile like this.
-bash-3.00$ vi /oradata2/1.pfile
undo_tablespace=UNDOTBS1
undo_management=AUTO
compatible=10.2.0.1.0
db_name=arjudba
sga_target=400M
That is without control_files parameter inside it. It is ok if you don't have any controlfile and you want to create a new one. If you don't give any location inside pfile then oracle will create controlfile in the location of $ORACLE_HOME/dbs/cntrlarjudba.dbf.
However if you specify control_files parameter inside pfile then oracle will attempt to create controlfile in the specified location. Like if you create pfile as
-bash-3.00$ vi /oradata2/1.pfile
undo_tablespace=UNDOTBS1
undo_management=AUTO
compatible=10.2.0.1.0
db_name=arjudba
control_files='/oradata2/arjudba/arjudba/control01.ctl','/oradata2/arjudba/arjud
ba/control02.ctl'
sga_target=400M
then create controlfile statement will create controlfile in the location of /oradata2/arjudba/arjudba/control01.ctl, and /oradata2/arjudba/arjudba/control02.ctl
If you don't give any location of control_file parameter inside pfile then further startup will fails with ORA-00205
Like,
SQL> startup
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 2019608 bytes
Variable Size 83889896 bytes
Database Buffers 117440512 bytes
Redo Buffers 6365184 bytes
ORA-00205: error in identifying control file, check alert log for more info
Wednesday, August 27, 2008
How to restore an Spfile from autobackup older than 7 days
Whenever we issue in RMAN, RMAN> restore spfile from autobackup; then restoring from autobackup stops at seven days and then it arises message channel ORA_DISK_1: no autobackup in 7 days found. For example,
RMAN> restore spfile from autobackup;
Starting restore at 31-AUG-08
using channel ORA_DISK_1
channel ORA_DISK_1: looking for autobackup on day: 20080831
channel ORA_DISK_1: looking for autobackup on day: 20080830
channel ORA_DISK_1: looking for autobackup on day: 20080829
channel ORA_DISK_1: looking for autobackup on day: 20080828
channel ORA_DISK_1: looking for autobackup on day: 20080827
channel ORA_DISK_1: looking for autobackup on day: 20080826
channel ORA_DISK_1: looking for autobackup on day: 20080825
channel ORA_DISK_1: no autobackup in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/31/2008 01:31:12
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece
Whenever you give RMAN to RESTORE SPFILE FROM AUTOBACKUP or RESTORE CONTROLFILE FROM AUTOBACKUP, RMAN begins the search on the current day or on the day specified with the SET UNTIL caluse. If no autobackup is found in the current or SET UNTIL day, RMAN checks the previous day. In this way RMAN by default checks for 7 days autobackup from the current or SET UNTIL day.
However if you want to extend your searching of autobackup more than 7 then you have to use MAXDAYS option with the RESTORE command.
For example,
RMAN>restore spfile from autobackup maxdays 30;
or
RMAN>restore controlfile from autobackup maxdays 30;
In these cases autobackup searches will be performed up to 30 days from the current or SET UNTIL day.
RMAN> restore spfile from autobackup;
Starting restore at 31-AUG-08
using channel ORA_DISK_1
channel ORA_DISK_1: looking for autobackup on day: 20080831
channel ORA_DISK_1: looking for autobackup on day: 20080830
channel ORA_DISK_1: looking for autobackup on day: 20080829
channel ORA_DISK_1: looking for autobackup on day: 20080828
channel ORA_DISK_1: looking for autobackup on day: 20080827
channel ORA_DISK_1: looking for autobackup on day: 20080826
channel ORA_DISK_1: looking for autobackup on day: 20080825
channel ORA_DISK_1: no autobackup in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/31/2008 01:31:12
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece
Whenever you give RMAN to RESTORE SPFILE FROM AUTOBACKUP or RESTORE CONTROLFILE FROM AUTOBACKUP, RMAN begins the search on the current day or on the day specified with the SET UNTIL caluse. If no autobackup is found in the current or SET UNTIL day, RMAN checks the previous day. In this way RMAN by default checks for 7 days autobackup from the current or SET UNTIL day.
However if you want to extend your searching of autobackup more than 7 then you have to use MAXDAYS option with the RESTORE command.
For example,
RMAN>restore spfile from autobackup maxdays 30;
or
RMAN>restore controlfile from autobackup maxdays 30;
In these cases autobackup searches will be performed up to 30 days from the current or SET UNTIL day.
Tuesday, August 5, 2008
How to know database initialization parameter in oracle
1)SHOW PARAMETER option: If you use SQL*Plus then you can use SHOW PARAMETER parameter_name to display the values of parameters currently in use. Note that with toad or tora you can't use it as it is SQL*plus option.
SQL>SHOW PARAMETERS sga_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 160M
2)Query from V$PARAMETER: You can query value column of this view to display the values of parameters currently in effect.
SQL> SELECT VALUE FROM v$PARAMETER WHERE name='sga_target';
VALUE
--------------------------------------------------------------------------------
167772160
3)Query from V$PARAMETER2: You can query value column of this view to display the values of parameters currently in effect. It is easier to distinguish list parameter values in this view because each list parameter value appears as a row.
4)Query from V$SPPARAMETER: You can query value column of this view to display the current contents of the server parameter file. The view returns FALSE values in the ISSPECIFIED column if a server parameter file is not being used by the instance.
5)Query from V$SYSTEM_PARAMETER: In this view it displays what will be the value if a new session inherits parameter values from the instance-wide values.
6)Query from V$SYSTEM_PARAMETER2: It displays the information about initialization parameters that are currently in effect for the instance, with each list parameter value appearing as a row in the view. A new session inherits parameter values from the instance-wide values.
Related Documents
Difference between V$parameter and v$spparameter
SQL>SHOW PARAMETERS sga_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 160M
2)Query from V$PARAMETER: You can query value column of this view to display the values of parameters currently in effect.
SQL> SELECT VALUE FROM v$PARAMETER WHERE name='sga_target';
VALUE
--------------------------------------------------------------------------------
167772160
3)Query from V$PARAMETER2: You can query value column of this view to display the values of parameters currently in effect. It is easier to distinguish list parameter values in this view because each list parameter value appears as a row.
4)Query from V$SPPARAMETER: You can query value column of this view to display the current contents of the server parameter file. The view returns FALSE values in the ISSPECIFIED column if a server parameter file is not being used by the instance.
5)Query from V$SYSTEM_PARAMETER: In this view it displays what will be the value if a new session inherits parameter values from the instance-wide values.
6)Query from V$SYSTEM_PARAMETER2: It displays the information about initialization parameters that are currently in effect for the instance, with each list parameter value appearing as a row in the view. A new session inherits parameter values from the instance-wide values.
Related Documents
Difference between V$parameter and v$spparameter
Thursday, July 31, 2008
ORA-00018, ORA-00020 maximum number of sessions exceeded
Error Description
Any operation or a new session creation is failed. Like,
ORA-00018: maximum number of sessions exceeded
ORA-00020: maximum number of processes (string) exceeded
In order to solve the problem you have to increase the value of the PROCESSES/ SESSIONS initialization parameter.
You can see the current settings of the SESSIONS and PROCESSES parameter value by querying from v$spparameter or by simply show parameter parameter_name.
SQL> col name format a30
SQL> col value format a10
SQL> select name, value from v$spparameter where name in ('processes','sessions');
NAME VALUE
------------------------------ ----------
processes 150
sessions
The explicitly value of the sessions parameter is not set and so it is left to blank. It's default value is derived from the processes parameter and value=1.1*process_parameter_value+5
You can calculate the default value of sessions parameter by,
SQL> select 1.1*value+5 "sessions par default value" from v$spparameter where name='processes';
sessions par default value
--------------------------
170
In order to change the value of the sessions you have to change it in spfile or pfile. Dynamically it can't be changed.
How to Solve the Problem
If your database start with spfile then,
Alter system set sessions=200 scope=spfile;
shutdown immediate;
startup;
If your database start with pfile then,
Open the pfile with an editor and edit the sessions parameter value and restart your database.
Similarly you can set the processes parameter.
Any operation or a new session creation is failed. Like,
ORA-00018: maximum number of sessions exceeded
ORA-00020: maximum number of processes (string) exceeded
In order to solve the problem you have to increase the value of the PROCESSES/ SESSIONS initialization parameter.
You can see the current settings of the SESSIONS and PROCESSES parameter value by querying from v$spparameter or by simply show parameter parameter_name.
SQL> col name format a30
SQL> col value format a10
SQL> select name, value from v$spparameter where name in ('processes','sessions');
NAME VALUE
------------------------------ ----------
processes 150
sessions
The explicitly value of the sessions parameter is not set and so it is left to blank. It's default value is derived from the processes parameter and value=1.1*process_parameter_value+5
You can calculate the default value of sessions parameter by,
SQL> select 1.1*value+5 "sessions par default value" from v$spparameter where name='processes';
sessions par default value
--------------------------
170
In order to change the value of the sessions you have to change it in spfile or pfile. Dynamically it can't be changed.
How to Solve the Problem
If your database start with spfile then,
Alter system set sessions=200 scope=spfile;
shutdown immediate;
startup;
If your database start with pfile then,
Open the pfile with an editor and edit the sessions parameter value and restart your database.
Similarly you can set the processes parameter.
Sunday, July 6, 2008
ORA-1000 Maximum Number of Cursors Exceeded
Error Description:
----------------------------------
Whenever you run any procedures or functions or any other application or transaction either database level or application level you get the following error,
ORA-01000: "maximum open cursors exceeded"
Cause of The Problem:
--------------------------------
The initialization parameter OPEN_CURSORS specifies the maximum number of open cursors a session can have at once. To run a application the program needs to open more cursor in a session than the OPEN_CURSORS and error arises.
To process a SQL statement, Oracle opens a work area called a private SQL area. This private SQL area stores information needed to execute a SQL statement. Cursors are stored in this area to keep track of information.
There are two types of cursor named implicit cursor and explicit cursor.
An IMPLICIT cursor is declared for all data definition and data manipulation statements. They are internal to Oracle and is handled by oracle. They are harder to tune because they are internal to Oracle.
But in order to handle the queries that return more than one row, you must declare an EXPLICIT cursor to retrieve all the information. They can be tuned by determining when you will open or close them.
Solution of The Problem:
--------------------------------------
You can solve the ORA-01000 error by tuning cursor usage at the database level and at the application level.
1. Tuning at the DATABASE LEVEL
-------------------------------------------------------
The initialization parameter OPEN_CURSORS specifies the maximum number of open cursors a session can have at once. The default value of it is 50 and the value ranges from 0 to 65535. By setting the highest value of it likely solve the ORA-01000 error. Like you can issue,
SQL> ALTER SYSTEM SET open_cursors=5000;
System altered.
SQL> show parameter open_curs
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 5000
2. Tuning at the APPLICATION LEVEL
----------------------------------------------------------------------
The three parameters that affect handling cursors at the application level are RELEASE_CURSOR, HOLD_CURSOR, MAXOPENCURSORS.
The default value of HOLD_CURSOR is NO which means that after Oracle executes a SQL statement the links to the cursor cache, memory, and parse locks are released and marked for reuse.
The default value of RELEASE_CURSOR is NO which means that after Oracle executes a SQL statement, the links to the cursor cache is maintained and not released.
To resolve the ORA-01000 error, you should set HOLD_CURSOR=NO and RELEASE_CURSOR=YES.
----------------------------------
Whenever you run any procedures or functions or any other application or transaction either database level or application level you get the following error,
ORA-01000: "maximum open cursors exceeded"
Cause of The Problem:
--------------------------------
The initialization parameter OPEN_CURSORS specifies the maximum number of open cursors a session can have at once. To run a application the program needs to open more cursor in a session than the OPEN_CURSORS and error arises.
To process a SQL statement, Oracle opens a work area called a private SQL area. This private SQL area stores information needed to execute a SQL statement. Cursors are stored in this area to keep track of information.
There are two types of cursor named implicit cursor and explicit cursor.
An IMPLICIT cursor is declared for all data definition and data manipulation statements. They are internal to Oracle and is handled by oracle. They are harder to tune because they are internal to Oracle.
But in order to handle the queries that return more than one row, you must declare an EXPLICIT cursor to retrieve all the information. They can be tuned by determining when you will open or close them.
Solution of The Problem:
--------------------------------------
You can solve the ORA-01000 error by tuning cursor usage at the database level and at the application level.
1. Tuning at the DATABASE LEVEL
-------------------------------------------------------
The initialization parameter OPEN_CURSORS specifies the maximum number of open cursors a session can have at once. The default value of it is 50 and the value ranges from 0 to 65535. By setting the highest value of it likely solve the ORA-01000 error. Like you can issue,
SQL> ALTER SYSTEM SET open_cursors=5000;
System altered.
SQL> show parameter open_curs
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 5000
2. Tuning at the APPLICATION LEVEL
----------------------------------------------------------------------
The three parameters that affect handling cursors at the application level are RELEASE_CURSOR, HOLD_CURSOR, MAXOPENCURSORS.
The default value of HOLD_CURSOR is NO which means that after Oracle executes a SQL statement the links to the cursor cache, memory, and parse locks are released and marked for reuse.
The default value of RELEASE_CURSOR is NO which means that after Oracle executes a SQL statement, the links to the cursor cache is maintained and not released.
To resolve the ORA-01000 error, you should set HOLD_CURSOR=NO and RELEASE_CURSOR=YES.
Wednesday, June 11, 2008
About STATISTICS_LEVEL parameter in Oracle
•The parameter STATISTICS_LEVEL is a string type parameter and it can take any of three values- TYPICAL, ALL or BASIC.
•It specifies the level of collection for database and operating system statistics.
•The default is TYPICAL which ensures collection of all major statistics required for database self-management functionality and provides best overall performance. So if it is set to TYPICAL then we don't need to bother about collection statistics by ANALYZE or by DBMS_STATS package.
•When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.
•If the STATISTICS_LEVEL parameter is set to BASIC it disables the collection of many of the important statistics required by Oracle Database features and functionality.
•STATISTICS_LEVEL parameter can be modified by both session level(using ALTER SESSION) or system level (using ALTER SYSTEM).
•To know the current settings of the parameter issue,
SQL> select value from v$parameter where name='statistics_level';
VALUE
--------------------------------------------------------------------------------
TYPICAL
Or,
•To know the status of the statistics or advisories controlled by the STATISTICS_LEVEL parameter issue the following query,
As we see as the current settings is TYPICAL so 'Timed OS Statistics' and 'Plan Execution Statistics' are disabled because they enable when STATISTICS_LEVEL is set to ALL. In the query we can also see by SESSION_SETTABLE parameter indicating whether the statistic/advisory can be set at the session level (YES) or not (NO).
•It specifies the level of collection for database and operating system statistics.
•The default is TYPICAL which ensures collection of all major statistics required for database self-management functionality and provides best overall performance. So if it is set to TYPICAL then we don't need to bother about collection statistics by ANALYZE or by DBMS_STATS package.
•When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.
•If the STATISTICS_LEVEL parameter is set to BASIC it disables the collection of many of the important statistics required by Oracle Database features and functionality.
•STATISTICS_LEVEL parameter can be modified by both session level(using ALTER SESSION) or system level (using ALTER SYSTEM).
•To know the current settings of the parameter issue,
SQL> select value from v$parameter where name='statistics_level';
VALUE
--------------------------------------------------------------------------------
TYPICAL
Or,
SQL> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
•To know the status of the statistics or advisories controlled by the STATISTICS_LEVEL parameter issue the following query,
SQL> COL STATISTICS_NAME FORMAT A50
SQL> SELECT STATISTICS_NAME,SESSION_STATUS,SYSTEM_STATUS,ACTIVATION_LEVEL,
SESSION_SETTABLE FROM V$STATISTICS_LEVEL;
STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT SES
-------------------------------------------------- -------- -------- ------- ---
Buffer Cache Advice ENABLED ENABLED TYPICAL NO
MTTR Advice ENABLED ENABLED TYPICAL NO
Timed Statistics ENABLED ENABLED TYPICAL YES
Timed OS Statistics DISABLED DISABLED ALL YES
Segment Level Statistics ENABLED ENABLED TYPICAL NO
PGA Advice ENABLED ENABLED TYPICAL NO
Plan Execution Statistics DISABLED DISABLED ALL YES
Shared Pool Advice ENABLED ENABLED TYPICAL NO
Modification Monitoring ENABLED ENABLED TYPICAL NO
Longops Statistics ENABLED ENABLED TYPICAL NO
Bind Data Capture ENABLED ENABLED TYPICAL NO
STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT SES
-------------------------------------------------- -------- -------- ------- ---
Ultrafast Latch Statistics ENABLED ENABLED TYPICAL NO
Threshold-based Alerts ENABLED ENABLED TYPICAL NO
Global Cache Statistics ENABLED ENABLED TYPICAL NO
Active Session History ENABLED ENABLED TYPICAL NO
Undo Advisor, Alerts and Fast Ramp up ENABLED ENABLED TYPICAL NO
16 rows selected.
As we see as the current settings is TYPICAL so 'Timed OS Statistics' and 'Plan Execution Statistics' are disabled because they enable when STATISTICS_LEVEL is set to ALL. In the query we can also see by SESSION_SETTABLE parameter indicating whether the statistic/advisory can be set at the session level (YES) or not (NO).
Sunday, May 25, 2008
Rules to Specify values in an initialization parameter
•Parameter within initialization file can be specified in any order.
•If a line starts with pound sign(#) the rest words of the line is ignored. # indicates it is comment.
•If OS is case sensitive then value of filename within initialization parameter is case sensitive.
•To enter several parameters in a line use spaces between the parameters. Like,
db_name=dbase sga_target=300M sga_max_size=300M
•The parameter that take multiple values enter multiple values enclosed in parentheses and separated by commas. Like,
control_files=('/oradata1/arju/control01.ctl','/oradata1/arju/control02.ctl')
Alternatively you can enter multiple values without parentheses and commas. Like,
control_files='/oradata1/arju/control01.ctl' '/oradata1/arju/control02.ctl'
•You can use the IFILE initialization parameter to embed the contents of another initialization parameter file into the current initialization parameter file.
•If you specify a parameter value in multiple location then the last executed value override the prior one since the parameter is executed in sequential order. For example, if my pfile contains value in the following order
sga_target=300M
open_cursor=100
sga_target=400M
then sga_target=400M is in effect.
•Enclose in quotation marks any parameter value that contains a special character.
For example you can specify DB_DOMAIN in any of following ways,
DB_DOMAIN = 'ARJU.ORACLE#.COM'
DB_DOMAIN = ARJU.ORACLE\#.COM
•If a line starts with pound sign(#) the rest words of the line is ignored. # indicates it is comment.
•If OS is case sensitive then value of filename within initialization parameter is case sensitive.
•To enter several parameters in a line use spaces between the parameters. Like,
db_name=dbase sga_target=300M sga_max_size=300M
•The parameter that take multiple values enter multiple values enclosed in parentheses and separated by commas. Like,
control_files=('/oradata1/arju/control01.ctl','/oradata1/arju/control02.ctl')
Alternatively you can enter multiple values without parentheses and commas. Like,
control_files='/oradata1/arju/control01.ctl' '/oradata1/arju/control02.ctl'
•You can use the IFILE initialization parameter to embed the contents of another initialization parameter file into the current initialization parameter file.
•If you specify a parameter value in multiple location then the last executed value override the prior one since the parameter is executed in sequential order. For example, if my pfile contains value in the following order
sga_target=300M
open_cursor=100
sga_target=400M
then sga_target=400M is in effect.
•Enclose in quotation marks any parameter value that contains a special character.
For example you can specify DB_DOMAIN in any of following ways,
DB_DOMAIN = 'ARJU.ORACLE#.COM'
DB_DOMAIN = ARJU.ORACLE\#.COM
Thursday, May 15, 2008
Startup fails with oracle error ORA-00119, ORA-00132
Error Description:
---------------------------
Database Startup fails with oracle error ORA-00119, ORA-00132
SQL> startup mount
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_DBASE'
Cause of The problem:
-------------------------
If the tnsnames.ora entry was used for the value of the LOCAL_LISTENER and the LOCAL_LISTENER entry inside tnsnames.ora is changed or tnsnames.ora file is moved and hence the tns alias to which the LOCAL_LISTENER parameter points is no longer valid. So, the entry inside spfile or pfile to be unresolvable, and the database will not start.
PMON must be able to resolve whatever value the LOCAL_LISTENER or REMOTE_LISTENER parameter is set to. Here LISTENER_DBASE is the name of the local listener.
Remember the LISTENER_DBASE is NOT the listener name reflected in the listener.ora file but rather it is an alias stored in the tnsnames.ora file.
Solution of The problem
-----------------------------
A)Correct the tnsnames.ora
i)Determine if the tns alias is good by using tnsping.
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 11:57:06
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
TNS-03505: Failed to resolve name
So it could not find the name inside tnsnames.ora.
ii)
Add the LISTENER_DBASE entry in the tnsnames.ora file.
SQL> !vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')
and run tnsping utility,
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:11:40
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')
TNS-12533: TNS:illegal ADDRESS parameters
So there is syntax error in the LISTENER_DBASE alias. Correct it as here add parenthesis.
SQL> !vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')))
iii)After correction run tnsping and start the database.
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:14:25
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase'))
OK (0 msec)
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
B)An alternative solution is to remove the LOCAL_LISTENER entry from the listener.ora file.
1)Create spfile from pfile if you don't have pfile upadted.
SQL>CREATE PFILE FROM SPFILE;
2)Open the pfile and remove the LOCAL_LISTENER entry.
$vi /oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase.ora
3)Create spfile from pfile.
SQL>CREATE PFILE FROM SPFILE;
4)Start the database.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
Recommendation:
------------------
Though you can set the value of LOCAL_LISTENER (or REMOTE_LISTENER parameter if used) as alias in tnsnames.ora like in this example but it is not recommended setting. Instead use a full address descriptor inside pfile or set dynamically inside spfile.
In order to set dynamically inside spfile and in memory log in with dba privileges and issue:
ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=yourhost)(port=yourport))" scope=both sid='instancename';
In my system,
SQL> ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=neptune)(port=1522))" scope=both sid='dbase';
SQL> show parameter local
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (address=(protocol=tcp)(host=n
eptune)(port=1522))
---------------------------
Database Startup fails with oracle error ORA-00119, ORA-00132
SQL> startup mount
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_DBASE'
Cause of The problem:
-------------------------
If the tnsnames.ora entry was used for the value of the LOCAL_LISTENER and the LOCAL_LISTENER entry inside tnsnames.ora is changed or tnsnames.ora file is moved and hence the tns alias to which the LOCAL_LISTENER parameter points is no longer valid. So, the entry inside spfile or pfile to be unresolvable, and the database will not start.
PMON must be able to resolve whatever value the LOCAL_LISTENER or REMOTE_LISTENER parameter is set to. Here LISTENER_DBASE is the name of the local listener.
Remember the LISTENER_DBASE is NOT the listener name reflected in the listener.ora file but rather it is an alias stored in the tnsnames.ora file.
Solution of The problem
-----------------------------
A)Correct the tnsnames.ora
i)Determine if the tns alias is good by using tnsping.
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 11:57:06
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
TNS-03505: Failed to resolve name
So it could not find the name inside tnsnames.ora.
ii)
Add the LISTENER_DBASE entry in the tnsnames.ora file.
SQL> !vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')
and run tnsping utility,
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:11:40
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')
TNS-12533: TNS:illegal ADDRESS parameters
So there is syntax error in the LISTENER_DBASE alias. Correct it as here add parenthesis.
SQL> !vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')))
iii)After correction run tnsping and start the database.
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:14:25
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase'))
OK (0 msec)
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
B)An alternative solution is to remove the LOCAL_LISTENER entry from the listener.ora file.
1)Create spfile from pfile if you don't have pfile upadted.
SQL>CREATE PFILE FROM SPFILE;
2)Open the pfile and remove the LOCAL_LISTENER entry.
$vi /oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase.ora
3)Create spfile from pfile.
SQL>CREATE PFILE FROM SPFILE;
4)Start the database.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
Recommendation:
------------------
Though you can set the value of LOCAL_LISTENER (or REMOTE_LISTENER parameter if used) as alias in tnsnames.ora like in this example but it is not recommended setting. Instead use a full address descriptor inside pfile or set dynamically inside spfile.
In order to set dynamically inside spfile and in memory log in with dba privileges and issue:
ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=yourhost)(port=yourport))" scope=both sid='instancename';
In my system,
SQL> ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=neptune)(port=1522))" scope=both sid='dbase';
SQL> show parameter local
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (address=(protocol=tcp)(host=n
eptune)(port=1522))
Wednesday, May 14, 2008
Startup fails with ORA-27102: out of memory Solaris-AMD64 Error
Scenario of The problem:
------------------------------
In my computer I have two database. One database is running smoothly but another database is not started whenever I invoke startup. It fails with error,
SQL> startup
ORA-27102: out of memory
Solaris-AMD64 Error: 22: Invalid argument
Reason of The problem:
------------------------
The database which could not start is because of the low memory on the system or in the sga_max_size there is high value set. So the system could not allocate so large memory as it does not have free so much. There may be other reasons like OS limitation in order of usage the memory. As in this case one database is ok (Both database are running on same user)and another database is failed so I suspect either low memory on the system or in the sga_max_size parameter inside spfile there is high value set.
Solution of The problem:
------------------------------
1)Set a lower amount of memory in the first database.
You can do it by, On dbase1,
SQL> alter system set sga_max_size=1600M scope=spfile;
System altered.
SQL> alter system set sga_target=1600M;
System altered.
SQL>shutdown
Now set ORACLE_SID and start the instance.
bash-3.00$ export ORACLE_SID=dupbase
bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 15 01:34:15 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
Or,
2)Lower the setting of SGA_MAX_SIZE, SGA_TARGET on the 2nd database. To do this create pfile from spfile.
SQL>create pfile from spfile;
And then edit the pfile parameter of SGA_MAX_SIZE and SGA_TARGET.
And start the database with the pfile.
SQL>STARTUP PFILE='pfile_name';
Later , Create spfile from pfile,
SQL>CREATE SPFILE from PFILE;
Related Documents
http://arjudba.blogspot.com/2008/12/expdp-fails-with-ora-39125-ora-04031.html
http://arjudba.blogspot.com/2009/05/ora-27100-shared-memory-realm-already.html
http://arjudba.blogspot.com/2008/05/startup-fails-with-oracle-error-ora.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-27302.html
http://arjudba.blogspot.com/2008/07/database-startup-fails-with-error-ora.html
http://arjudba.blogspot.com/2008/08/startup-fails-with-ora-01261-parameter.html
------------------------------
In my computer I have two database. One database is running smoothly but another database is not started whenever I invoke startup. It fails with error,
SQL> startup
ORA-27102: out of memory
Solaris-AMD64 Error: 22: Invalid argument
Reason of The problem:
------------------------
The database which could not start is because of the low memory on the system or in the sga_max_size there is high value set. So the system could not allocate so large memory as it does not have free so much. There may be other reasons like OS limitation in order of usage the memory. As in this case one database is ok (Both database are running on same user)and another database is failed so I suspect either low memory on the system or in the sga_max_size parameter inside spfile there is high value set.
Solution of The problem:
------------------------------
1)Set a lower amount of memory in the first database.
You can do it by, On dbase1,
SQL> alter system set sga_max_size=1600M scope=spfile;
System altered.
SQL> alter system set sga_target=1600M;
System altered.
SQL>shutdown
Now set ORACLE_SID and start the instance.
bash-3.00$ export ORACLE_SID=dupbase
bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 15 01:34:15 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
Or,
2)Lower the setting of SGA_MAX_SIZE, SGA_TARGET on the 2nd database. To do this create pfile from spfile.
SQL>create pfile from spfile;
And then edit the pfile parameter of SGA_MAX_SIZE and SGA_TARGET.
And start the database with the pfile.
SQL>STARTUP PFILE='pfile_name';
Later , Create spfile from pfile,
SQL>CREATE SPFILE from PFILE;
Related Documents
http://arjudba.blogspot.com/2008/12/expdp-fails-with-ora-39125-ora-04031.html
http://arjudba.blogspot.com/2009/05/ora-27100-shared-memory-realm-already.html
http://arjudba.blogspot.com/2008/05/startup-fails-with-oracle-error-ora.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-27302.html
http://arjudba.blogspot.com/2008/07/database-startup-fails-with-error-ora.html
http://arjudba.blogspot.com/2008/08/startup-fails-with-ora-01261-parameter.html
Monday, May 5, 2008
How to Restore spfile from backup
1.Connect to target Database with RMAN.
i)If you have lost your spfile while your database is running then,
RMAN>CONNECT TARGET /
ii)If your database is not up and you don't use recovery catalog then use,
RMAN>CONNECT TARGET /
RMAN>SET DBID 3386862614
2)Start the instance with dummy parameter file.
RMAN>STARTUP FORCE NOMOUNT
3)Restore server parameter file.
To restore in default location,
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
To restore in another location,
RMAN> RESTORE SPFILE TO 'new_location' FROM AUTOBACKUP;
If you want to restore to a pfile then use,
RMAN> RESTORE SPFILE TO PFILE '/tmp/initTEMP.ora';
4)Start the instance.
RMAN>STARTUP;
Related Documents:
----------------------------
How Spfile can be Recovered
i)If you have lost your spfile while your database is running then,
RMAN>CONNECT TARGET /
ii)If your database is not up and you don't use recovery catalog then use,
RMAN>CONNECT TARGET /
RMAN>SET DBID 3386862614
2)Start the instance with dummy parameter file.
RMAN>STARTUP FORCE NOMOUNT
3)Restore server parameter file.
To restore in default location,
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
To restore in another location,
RMAN> RESTORE SPFILE TO 'new_location' FROM AUTOBACKUP;
If you want to restore to a pfile then use,
RMAN> RESTORE SPFILE TO PFILE '/tmp/initTEMP.ora';
4)Start the instance.
RMAN>STARTUP;
Related Documents:
----------------------------
How Spfile can be Recovered
Thursday, April 17, 2008
Types of Initialization Parameters in Oracle
There are different types of initialization parameters in oracle, such as,
1)Derived Parameters
2)Operating System-Dependent Parameters
3)Variable Parameters
1)Derived Parameters:
-----------------------
As the name indicates these parameter values are calculated from the values of other parameters. Normally, you should not alter values for derived parameters, but if you do, then the value you specify will override the calculated value.
For example, the default value of the SESSIONS parameter is derived from the value of the PROCESSES parameter. If the value of PROCESSES changes, then the default value of SESSIONS changes as well, unless you override it with a specified value.
2)Operating System-Dependent Parameters
--------------------------------------
The valid value of value ranges of these parameters are host Operating System dependent. For example, the value of the parameter DB_BLOCK_SIZE, has an operating system-dependent default value.
3)Variable Parameters
----------------------------
The variable initialization parameters offer the most potential for improving system performance.
Some variable parameters set capacity limits but do not affect performance. For example, when the value of OPEN_CURSORS is 10, a user process attempting to open its eleventh cursor receives an error. Other variable parameters affect performance but do not impose absolute limits.
1)Derived Parameters
2)Operating System-Dependent Parameters
3)Variable Parameters
1)Derived Parameters:
-----------------------
As the name indicates these parameter values are calculated from the values of other parameters. Normally, you should not alter values for derived parameters, but if you do, then the value you specify will override the calculated value.
For example, the default value of the SESSIONS parameter is derived from the value of the PROCESSES parameter. If the value of PROCESSES changes, then the default value of SESSIONS changes as well, unless you override it with a specified value.
2)Operating System-Dependent Parameters
--------------------------------------
The valid value of value ranges of these parameters are host Operating System dependent. For example, the value of the parameter DB_BLOCK_SIZE, has an operating system-dependent default value.
3)Variable Parameters
----------------------------
The variable initialization parameters offer the most potential for improving system performance.
Some variable parameters set capacity limits but do not affect performance. For example, when the value of OPEN_CURSORS is 10, a user process attempting to open its eleventh cursor receives an error. Other variable parameters affect performance but do not impose absolute limits.
Subscribe to:
Posts (Atom)