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 Pfile. Show all posts
Showing posts with label Pfile. 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
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
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.
Saturday, June 14, 2008
Parameters that enable and control Query Optimizer Features
You know that Query optimizer is responsible to determine the best execution/explain plan. In many cases you may be astonished that the data is same in both database and you have gather statistics but both database give different execution plan. The possible reason for it it the variation of initialization parameter between two database. Now in the later section in this post we will have a look at the initialization parameter that affect the optimizer to determine execution plan.
A)Enable Query Optimizer Feature
--------------------------------------------
OPTIMIZER_FEATURES_ENABLE Parameter
------------------------------------------------
•It is a string type parameter and takes oracle version number as argument.
•Based on this parameter settings it is determined how oracle optimizer behaves.
•Every new release of oracle version comes with new feature for optimizer. Thus new version of optimizer can collect extra features of a query based on which execution plan can changes. If you upgrade your oracle to newer version and your don't want to change your execution plan according to new one (keep like older) then you can set this parameter to older one.
•The valid values of this parameter can be,
8.0.0 | 8.0.3 | 8.0.4 | 8.0.5 | 8.0.6 | 8.0.7 | 8.1.0 | 8.1.3 | 8.1.4 | 8.1.5 | 8.1.6 | 8.1.7 | 9.0.0 | 9.0.1 | 9.2.0 | 10.0.0 | 10.1.0 | 10.1.0.3 | 10.1.0.4 | 10.2.0.1|10.2.0.2|10.2.0.3| etc.
B)Control the Behavior of the Query Optimizer
---------------------------------------------------------------------------
Here is the list of initialization parameters that can be used to control the behavior of the query optimizer.
1)CURSOR_SHARING
2)DB_FILE_MULTIBLOCK_READ_COUNT
3)OPTIMIZER_INDEX_CACHING
4)OPTIMIZER_INDEX_COST_ADJ
5)OPTIMIZER_MODE
6)PGA_AGGREGATE_TARGET
7)STAR_TRANSFORMATION_ENABLED
All of these parameters are discussed in topic Parameters that control the behavior of Query Optimizer.
A)Enable Query Optimizer Feature
--------------------------------------------
OPTIMIZER_FEATURES_ENABLE Parameter
------------------------------------------------
•It is a string type parameter and takes oracle version number as argument.
•Based on this parameter settings it is determined how oracle optimizer behaves.
•Every new release of oracle version comes with new feature for optimizer. Thus new version of optimizer can collect extra features of a query based on which execution plan can changes. If you upgrade your oracle to newer version and your don't want to change your execution plan according to new one (keep like older) then you can set this parameter to older one.
•The valid values of this parameter can be,
8.0.0 | 8.0.3 | 8.0.4 | 8.0.5 | 8.0.6 | 8.0.7 | 8.1.0 | 8.1.3 | 8.1.4 | 8.1.5 | 8.1.6 | 8.1.7 | 9.0.0 | 9.0.1 | 9.2.0 | 10.0.0 | 10.1.0 | 10.1.0.3 | 10.1.0.4 | 10.2.0.1|10.2.0.2|10.2.0.3| etc.
B)Control the Behavior of the Query Optimizer
---------------------------------------------------------------------------
Here is the list of initialization parameters that can be used to control the behavior of the query optimizer.
1)CURSOR_SHARING
2)DB_FILE_MULTIBLOCK_READ_COUNT
3)OPTIMIZER_INDEX_CACHING
4)OPTIMIZER_INDEX_COST_ADJ
5)OPTIMIZER_MODE
6)PGA_AGGREGATE_TARGET
7)STAR_TRANSFORMATION_ENABLED
All of these parameters are discussed in topic Parameters that control the behavior of Query Optimizer.
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
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)