Problem Description
While installing oracle on my windows machine it fails with the message,
OUI-10133: Invalid staging area. There are no top level components for Windows NT, Windows 2000 available for installation in this staging area.
On linux system this message is like,
OUI-10133: Invalid staging area. There are no top level components for Linux available for installation in this staging area.
Cause of the problem
There are many causes by which this problem can happen.
1)The product.xml directory is not found and hence the problem occurs. In the oraparam.ini the source location is specified to product.xml but no product.xml exist in the stage directory.
2)Someone has deleted the products.xml file from its location and installer could not find it.
3)You have downloaded oracle from e-delivery.oracle.com which contains three zip files and you extracted three zip files into different locations.
4)The staging area i.e stage folder is corrupted.
5)The media is corrupted.
Solution of the Problem
1)Open oraparam.ini configuration file which resides in the install directory both in windows and linux.
2)Within the file you will see there is SOURCE parameter and within it the location of products.xml is specified.
3)Go to that location and be sure that products.xml exist. There might be several reasons that installer could not find any. Suppose you copied oracle software from network and it is not fully copied. Someone deleted file from stage directory.
4)If you download zipped file of oracle software from e-delivery.oracle.com for 11g, then there will be three zip file. Unzip those three files into the same directory and then start installing the software.
5)After downloading software do cksum on source and verify with cksum in the OTN.
6)Get a fresh copy of oracle software and install.
Showing posts with label Ora Install. Show all posts
Showing posts with label Ora Install. Show all posts
Tuesday, April 21, 2009
Friday, January 2, 2009
Setting an Oracle Environment variable – ORACLE_HOME
What is ORACLE_HOME used for?
* The ORACLE_HOME is an environment variable which is used to set and define the path of Oracle Home (server) Directory.
* The ORACLE_HOME directory will have the sub directories, binaries, executables, programs, scripts, etc. for the Oracle Database.
* This directory can be used by any user who wants to use the particular database.
* If the ORACLE_HOME variable is defined as an environment variable, then during the installation process, the Oracle Home Path will be set to the directory defined as default. If the variable is not defined, then the Oracle will take its own default location. i.e. The ORACLE_HOME variable does not have to be preset as an environment variable, it can be set during the installation process.
* Basically The ORACLE_HOME variable is in the following ORACLE_BASE directory:
ORACLE_HOME=$ORACLE_BASE/product/10.2.0.
What is ORACLE_BASE used for?
* The ORACLE_BASE is also an environment variable to define the base/root level directory where you will have the Oracle Database directory tree - ORACLE_HOME defined under the ORACLE_BASE directory.
* Basically, The ORACLE_BASE directory is a higher-level directory, than ORACLE_HOME, that you can use to install the various Oracle Software Products and the same Oracle base directory can be used for more than one installation.
Note: If you did not set the ORACLE_BASE environment variable before starting OUI, the Oracle home directory is created in an app/username/directory on the first existing and writable directory from /u01 through /u09 for UNIX and Linux systems, or on the disk drive with the most available space for Windows systems. If /u01 through /u09 does not exist on the UNIX or Linux system, then the default location is user_home_directory/app/username.
How to check if ORACLE_HOME is set already?
On Unix/Linux Systems:
Basically, before or after the Oracle Database is installed, the oracle user profile, the environment variable file, is prepared where all the required environment variables for Oracle are set. i.e. ORACLE_BASE, ORACLE_HOME, ORACLE_SID,PATH, LD_LIBRARY_PATH, NLS_LANG, etc.
The user profile file can be
.bash_profile – Bash Shell
.profile – Bourne Shell or Korn shell
.login – C Shell
Note: This user profile file will be under user’s home directory i.e. $HOME/.bash_profile
To check specific environment variable set:
$ echo $ORACLE_HOME
To check all the environment variables set:
$ env
On Windows Systems:
To check specific environment variable set:
C:\> set ORACLE_HOME
OR
C:\echo %ORACLE_HOME%
To check all the environment variables set:
C:\> set
Or
C:\> env
Other way, to check the ORACLE_HOME, is as follows.
Start -> Run -> Regedit (enter) -> HKEY_LOCAL_MACHINE -> SOFTWARE –> ORACLE
i.e. My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
How to check using sqlplus command:
To Find the ORACLE_HOME path in Oracle Database
In 9i:
SELECT substr(file_spec,1,instr(file_spec,'lib')-2) ORACLE_HOME FROM dba_libraries
WHERE library_name='DBMS_SUMADV_LIB';
In 10g:
SQL > var OHM varchar2(100);
SQL > EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;
SQL > PRINT OHM
Linux/Unix:
echo $ORACLE_HOME
Windows:
start - run - regedit (enter) - HKEY_LOCAL_MACHINE - SOFTWARE - ORACLE
Type the below command at prompt if the environment variable is defined for ORACLE_HOME
c:\> set oracle_home (ENTER)
How to set the ORACLE_HOME environment variable?
On Unix/Linux Systems:
Define the ORACLE_HOME value in the user profile file i.e. .bash_profile or .profile
ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export ORACLE_HOME
Source the user profile as follows:
Bash shell:
$ . ./.bash_profile
Bourne shell or Korn shell:
$ . ./.profile
C shell:
% source ./.login
If no profile file is set with environment variables, then physically also be set as follows:
Bourne, Bash, or Korn shell:
$ ORACLE_BASE=/oracle/app
$ export ORACLE_BASE
$ ORACLE_HOME=$ORACLE_BASE/product/10.2.0
$ export ORACLE_HOME
C Shell:
% setenv ORACLE_BASE /oracle/app
% setenv ORACLE_HOME /oracle/app/product/10.2.0
On Windows Systems:
My Computer -> Properties -> Advanced -> Environment Variables -> System Variables -> New/Edit/Delete (to set the variables)
After setting the environment variables as above, open a fresh CMD tool and check whether they set properly or not. Do not try on already opened CMD tool to make sure the variables set or not.
Another way to physically set the variables as follow at the DOS prompt:
C:\> set ORACLE_HOME=C:\oracle\app\product\10.2.0
C:\> echo %ORACLE_HOME%
Note: I would suggest to refer the Oracle Documentation on Installation where these environment variables are discussed and explained with the enough information.
* The ORACLE_HOME is an environment variable which is used to set and define the path of Oracle Home (server) Directory.
* The ORACLE_HOME directory will have the sub directories, binaries, executables, programs, scripts, etc. for the Oracle Database.
* This directory can be used by any user who wants to use the particular database.
* If the ORACLE_HOME variable is defined as an environment variable, then during the installation process, the Oracle Home Path will be set to the directory defined as default. If the variable is not defined, then the Oracle will take its own default location. i.e. The ORACLE_HOME variable does not have to be preset as an environment variable, it can be set during the installation process.
* Basically The ORACLE_HOME variable is in the following ORACLE_BASE directory:
ORACLE_HOME=$ORACLE_BASE/product/10.2.0.
What is ORACLE_BASE used for?
* The ORACLE_BASE is also an environment variable to define the base/root level directory where you will have the Oracle Database directory tree - ORACLE_HOME defined under the ORACLE_BASE directory.
* Basically, The ORACLE_BASE directory is a higher-level directory, than ORACLE_HOME, that you can use to install the various Oracle Software Products and the same Oracle base directory can be used for more than one installation.
Note: If you did not set the ORACLE_BASE environment variable before starting OUI, the Oracle home directory is created in an app/username/directory on the first existing and writable directory from /u01 through /u09 for UNIX and Linux systems, or on the disk drive with the most available space for Windows systems. If /u01 through /u09 does not exist on the UNIX or Linux system, then the default location is user_home_directory/app/username.
How to check if ORACLE_HOME is set already?
On Unix/Linux Systems:
Basically, before or after the Oracle Database is installed, the oracle user profile, the environment variable file, is prepared where all the required environment variables for Oracle are set. i.e. ORACLE_BASE, ORACLE_HOME, ORACLE_SID,PATH, LD_LIBRARY_PATH, NLS_LANG, etc.
The user profile file can be
.bash_profile – Bash Shell
.profile – Bourne Shell or Korn shell
.login – C Shell
Note: This user profile file will be under user’s home directory i.e. $HOME/.bash_profile
To check specific environment variable set:
$ echo $ORACLE_HOME
To check all the environment variables set:
$ env
On Windows Systems:
To check specific environment variable set:
C:\> set ORACLE_HOME
OR
C:\echo %ORACLE_HOME%
To check all the environment variables set:
C:\> set
Or
C:\> env
Other way, to check the ORACLE_HOME, is as follows.
Start -> Run -> Regedit (enter) -> HKEY_LOCAL_MACHINE -> SOFTWARE –> ORACLE
i.e. My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
How to check using sqlplus command:
To Find the ORACLE_HOME path in Oracle Database
In 9i:
SELECT substr(file_spec,1,instr(file_spec,'lib')-2) ORACLE_HOME FROM dba_libraries
WHERE library_name='DBMS_SUMADV_LIB';
In 10g:
SQL > var OHM varchar2(100);
SQL > EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;
SQL > PRINT OHM
Linux/Unix:
echo $ORACLE_HOME
Windows:
start - run - regedit (enter) - HKEY_LOCAL_MACHINE - SOFTWARE - ORACLE
Type the below command at prompt if the environment variable is defined for ORACLE_HOME
c:\> set oracle_home (ENTER)
How to set the ORACLE_HOME environment variable?
On Unix/Linux Systems:
Define the ORACLE_HOME value in the user profile file i.e. .bash_profile or .profile
ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export ORACLE_HOME
Source the user profile as follows:
Bash shell:
$ . ./.bash_profile
Bourne shell or Korn shell:
$ . ./.profile
C shell:
% source ./.login
If no profile file is set with environment variables, then physically also be set as follows:
Bourne, Bash, or Korn shell:
$ ORACLE_BASE=/oracle/app
$ export ORACLE_BASE
$ ORACLE_HOME=$ORACLE_BASE/product/10.2.0
$ export ORACLE_HOME
C Shell:
% setenv ORACLE_BASE /oracle/app
% setenv ORACLE_HOME /oracle/app/product/10.2.0
On Windows Systems:
My Computer -> Properties -> Advanced -> Environment Variables -> System Variables -> New/Edit/Delete (to set the variables)
After setting the environment variables as above, open a fresh CMD tool and check whether they set properly or not. Do not try on already opened CMD tool to make sure the variables set or not.
Another way to physically set the variables as follow at the DOS prompt:
C:\> set ORACLE_HOME=C:\oracle\app\product\10.2.0
C:\> echo %ORACLE_HOME%
Note: I would suggest to refer the Oracle Documentation on Installation where these environment variables are discussed and explained with the enough information.
Setting an Oracle Environment variable – ORACLE_HOME
What is ORACLE_HOME used for?
* The ORACLE_HOME is an environment variable which is used to set and define the path of Oracle Home (server) Directory.
* The ORACLE_HOME directory will have the sub directories, binaries, executables, programs, scripts, etc. for the Oracle Database.
* This directory can be used by any user who wants to use the particular database.
* If the ORACLE_HOME variable is defined as an environment variable, then during the installation process, the Oracle Home Path will be set to the directory defined as default. If the variable is not defined, then the Oracle will take its own default location. i.e. The ORACLE_HOME variable does not have to be preset as an environment variable, it can be set during the installation process.
* Basically The ORACLE_HOME variable is in the following ORACLE_BASE directory:
ORACLE_HOME=$ORACLE_BASE/product/10.2.0.
What is ORACLE_BASE used for?
* The ORACLE_BASE is also an environment variable to define the base/root level directory where you will have the Oracle Database directory tree - ORACLE_HOME defined under the ORACLE_BASE directory.
* Basically, The ORACLE_BASE directory is a higher-level directory, than ORACLE_HOME, that you can use to install the various Oracle Software Products and the same Oracle base directory can be used for more than one installation.
Note: If you did not set the ORACLE_BASE environment variable before starting OUI, the Oracle home directory is created in an app/username/directory on the first existing and writable directory from /u01 through /u09 for UNIX and Linux systems, or on the disk drive with the most available space for Windows systems. If /u01 through /u09 does not exist on the UNIX or Linux system, then the default location is user_home_directory/app/username.
How to check if ORACLE_HOME is set already?
On Unix/Linux Systems:
Basically, before or after the Oracle Database is installed, the oracle user profile, the environment variable file, is prepared where all the required environment variables for Oracle are set. i.e. ORACLE_BASE, ORACLE_HOME, ORACLE_SID,PATH, LD_LIBRARY_PATH, NLS_LANG, etc.
The user profile file can be
.bash_profile – Bash Shell
.profile – Bourne Shell or Korn shell
.login – C Shell
Note: This user profile file will be under user’s home directory i.e. $HOME/.bash_profile
To check specific environment variable set:
$ echo $ORACLE_HOME
To check all the environment variables set:
$ env
On Windows Systems:
To check specific environment variable set:
C:\> set ORACLE_HOME
OR
C:\echo %ORACLE_HOME%
To check all the environment variables set:
C:\> set
Or
C:\> env
Other way, to check the ORACLE_HOME, is as follows.
Start -> Run -> Regedit (enter) -> HKEY_LOCAL_MACHINE -> SOFTWARE –> ORACLE
i.e. My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
How to check using sqlplus command:
To Find the ORACLE_HOME path in Oracle Database
In 9i:
SELECT substr(file_spec,1,instr(file_spec,'lib')-2) ORACLE_HOME FROM dba_libraries
WHERE library_name='DBMS_SUMADV_LIB';
In 10g:
SQL > var OHM varchar2(100);
SQL > EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;
SQL > PRINT OHM
Linux/Unix:
echo $ORACLE_HOME
Windows:
start - run - regedit (enter) - HKEY_LOCAL_MACHINE - SOFTWARE - ORACLE
Type the below command at prompt if the environment variable is defined for ORACLE_HOME
c:\> set oracle_home (ENTER)
How to set the ORACLE_HOME environment variable?
On Unix/Linux Systems:
Define the ORACLE_HOME value in the user profile file i.e. .bash_profile or .profile
ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export ORACLE_HOME
Source the user profile as follows:
Bash shell:
$ . ./.bash_profile
Bourne shell or Korn shell:
$ . ./.profile
C shell:
% source ./.login
If no profile file is set with environment variables, then physically also be set as follows:
Bourne, Bash, or Korn shell:
$ ORACLE_BASE=/oracle/app
$ export ORACLE_BASE
$ ORACLE_HOME=$ORACLE_BASE/product/10.2.0
$ export ORACLE_HOME
C Shell:
% setenv ORACLE_BASE /oracle/app
% setenv ORACLE_HOME /oracle/app/product/10.2.0
On Windows Systems:
My Computer -> Properties -> Advanced -> Environment Variables -> System Variables -> New/Edit/Delete (to set the variables)
After setting the environment variables as above, open a fresh CMD tool and check whether they set properly or not. Do not try on already opened CMD tool to make sure the variables set or not.
Another way to physically set the variables as follow at the DOS prompt:
C:\> set ORACLE_HOME=C:\oracle\app\product\10.2.0
C:\> echo %ORACLE_HOME%
Note: I would suggest to refer the Oracle Documentation on Installation where these environment variables are discussed and explained with the enough information.
* The ORACLE_HOME is an environment variable which is used to set and define the path of Oracle Home (server) Directory.
* The ORACLE_HOME directory will have the sub directories, binaries, executables, programs, scripts, etc. for the Oracle Database.
* This directory can be used by any user who wants to use the particular database.
* If the ORACLE_HOME variable is defined as an environment variable, then during the installation process, the Oracle Home Path will be set to the directory defined as default. If the variable is not defined, then the Oracle will take its own default location. i.e. The ORACLE_HOME variable does not have to be preset as an environment variable, it can be set during the installation process.
* Basically The ORACLE_HOME variable is in the following ORACLE_BASE directory:
ORACLE_HOME=$ORACLE_BASE/product/10.2.0.
What is ORACLE_BASE used for?
* The ORACLE_BASE is also an environment variable to define the base/root level directory where you will have the Oracle Database directory tree - ORACLE_HOME defined under the ORACLE_BASE directory.
* Basically, The ORACLE_BASE directory is a higher-level directory, than ORACLE_HOME, that you can use to install the various Oracle Software Products and the same Oracle base directory can be used for more than one installation.
Note: If you did not set the ORACLE_BASE environment variable before starting OUI, the Oracle home directory is created in an app/username/directory on the first existing and writable directory from /u01 through /u09 for UNIX and Linux systems, or on the disk drive with the most available space for Windows systems. If /u01 through /u09 does not exist on the UNIX or Linux system, then the default location is user_home_directory/app/username.
How to check if ORACLE_HOME is set already?
On Unix/Linux Systems:
Basically, before or after the Oracle Database is installed, the oracle user profile, the environment variable file, is prepared where all the required environment variables for Oracle are set. i.e. ORACLE_BASE, ORACLE_HOME, ORACLE_SID,PATH, LD_LIBRARY_PATH, NLS_LANG, etc.
The user profile file can be
.bash_profile – Bash Shell
.profile – Bourne Shell or Korn shell
.login – C Shell
Note: This user profile file will be under user’s home directory i.e. $HOME/.bash_profile
To check specific environment variable set:
$ echo $ORACLE_HOME
To check all the environment variables set:
$ env
On Windows Systems:
To check specific environment variable set:
C:\> set ORACLE_HOME
OR
C:\echo %ORACLE_HOME%
To check all the environment variables set:
C:\> set
Or
C:\> env
Other way, to check the ORACLE_HOME, is as follows.
Start -> Run -> Regedit (enter) -> HKEY_LOCAL_MACHINE -> SOFTWARE –> ORACLE
i.e. My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
How to check using sqlplus command:
To Find the ORACLE_HOME path in Oracle Database
In 9i:
SELECT substr(file_spec,1,instr(file_spec,'lib')-2) ORACLE_HOME FROM dba_libraries
WHERE library_name='DBMS_SUMADV_LIB';
In 10g:
SQL > var OHM varchar2(100);
SQL > EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;
SQL > PRINT OHM
Linux/Unix:
echo $ORACLE_HOME
Windows:
start - run - regedit (enter) - HKEY_LOCAL_MACHINE - SOFTWARE - ORACLE
Type the below command at prompt if the environment variable is defined for ORACLE_HOME
c:\> set oracle_home (ENTER)
How to set the ORACLE_HOME environment variable?
On Unix/Linux Systems:
Define the ORACLE_HOME value in the user profile file i.e. .bash_profile or .profile
ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export ORACLE_HOME
Source the user profile as follows:
Bash shell:
$ . ./.bash_profile
Bourne shell or Korn shell:
$ . ./.profile
C shell:
% source ./.login
If no profile file is set with environment variables, then physically also be set as follows:
Bourne, Bash, or Korn shell:
$ ORACLE_BASE=/oracle/app
$ export ORACLE_BASE
$ ORACLE_HOME=$ORACLE_BASE/product/10.2.0
$ export ORACLE_HOME
C Shell:
% setenv ORACLE_BASE /oracle/app
% setenv ORACLE_HOME /oracle/app/product/10.2.0
On Windows Systems:
My Computer -> Properties -> Advanced -> Environment Variables -> System Variables -> New/Edit/Delete (to set the variables)
After setting the environment variables as above, open a fresh CMD tool and check whether they set properly or not. Do not try on already opened CMD tool to make sure the variables set or not.
Another way to physically set the variables as follow at the DOS prompt:
C:\> set ORACLE_HOME=C:\oracle\app\product\10.2.0
C:\> echo %ORACLE_HOME%
Note: I would suggest to refer the Oracle Documentation on Installation where these environment variables are discussed and explained with the enough information.
Configuring Shared Server
We have recently configured shared server in some of our databases. Steps to follow to enable shared server are below.
The following configuration is on Enterprise Edition 9.2.0.5. The numbers used in the settings shown are only examples. We are running an OLTP system with about 2000 concurrent users with these parameters without any problem.
The parameters you need to set for the shared server and their default values are:
NAME VALUE
------------------------------ ----------
circuits 0
dispatchers
max_dispatchers 5
max_shared_servers 20
mts_circuits 0
mts_dispatchers
mts_max_dispatchers 5
shared_server_sessions 0
shared_servers 0
The ones starting with mts are for backward compatibility, setting circuits, dispatchers and max_dispatchers or mts_circuits, mts_dispatchers and mts_max_dispatchers is the same and is up to you.
If we go over these parameters one by one:
Circuits: This is a static parameter and it defaults to the sessions parameter when the shared server is used. You can leave it to the defalt value if your users are not using database links extensively. Because every session connected through the shared server adds to the circuits count for each distinct database link it opens. In our case we had sessions=3500 and circuits=3500 derived from that sessions parameter. But each user queried from two or three database links, so when the session count was about 1500 we exhausted circuits and began to get “error 18 creating virtual circuit” errors. So, you may need to set this parameter explicitly to a higher value if you are using database links.
Dispatchers: This parameter is said to be dynamic in Database Reference but if you have it as null in your database like the above settings you can not set it dynamically.
SQL> alter system set dispatchers='(protocol=tcp)(dispatchers=2)';
alter system set dispatchers='(protocol=tcp)(dispatchers=2)'
*
ERROR at line 1:
ORA-00105: dispatching mechanism not configured for network protocol
(ADDRESS=(PARTIAL=YES)(PROTOCOL=tcp))
You can not even set it with scope=spfile.
SQL> alter system set dispatchers='(protocol=tcp)(dispatchers=2)' scope=spfile;
alter system set dispatchers='(protocol=tcp)(dispatchers=2)' scope=spfile
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
If you have it null on your system, the only way to set this is to create a pfile from the spfile and put it in there and then shutdown the database, create a new spfile and startup.
Shared_servers: This is a dynamic parameter and defaults to 1 when shared server is used.
Max_shared_servers: This is a static parameter and defaults to 20 or 2*shared_servers.
The steps we followed were:
SQL> alter system set circuits = 10000 scope=spfile;
System altered.
SQL> alter system set max_shared_servers=200 scope=spfile;
System altered.
SQL> alter system set shared_servers=50 scope=spfile;
System altered.
SQL> create pfile from spfile;
File created.
We have inserted the following line to the pfile created.
dispatchers='(protocol=tcp)(dispatchers=5)'
Then shutdown, create spfile from pfile and startup the instance.
Configuring Shared Server
We have recently configured shared server in some of our databases. Steps to follow to enable shared server are below.
The following configuration is on Enterprise Edition 9.2.0.5. The numbers used in the settings shown are only examples. We are running an OLTP system with about 2000 concurrent users with these parameters without any problem.
The parameters you need to set for the shared server and their default values are:
NAME VALUE
------------------------------ ----------
circuits 0
dispatchers
max_dispatchers 5
max_shared_servers 20
mts_circuits 0
mts_dispatchers
mts_max_dispatchers 5
shared_server_sessions 0
shared_servers 0
The ones starting with mts are for backward compatibility, setting circuits, dispatchers and max_dispatchers or mts_circuits, mts_dispatchers and mts_max_dispatchers is the same and is up to you.
If we go over these parameters one by one:
Circuits: This is a static parameter and it defaults to the sessions parameter when the shared server is used. You can leave it to the defalt value if your users are not using database links extensively. Because every session connected through the shared server adds to the circuits count for each distinct database link it opens. In our case we had sessions=3500 and circuits=3500 derived from that sessions parameter. But each user queried from two or three database links, so when the session count was about 1500 we exhausted circuits and began to get “error 18 creating virtual circuit” errors. So, you may need to set this parameter explicitly to a higher value if you are using database links.
Dispatchers: This parameter is said to be dynamic in Database Reference but if you have it as null in your database like the above settings you can not set it dynamically.
SQL> alter system set dispatchers='(protocol=tcp)(dispatchers=2)';
alter system set dispatchers='(protocol=tcp)(dispatchers=2)'
*
ERROR at line 1:
ORA-00105: dispatching mechanism not configured for network protocol
(ADDRESS=(PARTIAL=YES)(PROTOCOL=tcp))
You can not even set it with scope=spfile.
SQL> alter system set dispatchers='(protocol=tcp)(dispatchers=2)' scope=spfile;
alter system set dispatchers='(protocol=tcp)(dispatchers=2)' scope=spfile
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
If you have it null on your system, the only way to set this is to create a pfile from the spfile and put it in there and then shutdown the database, create a new spfile and startup.
Shared_servers: This is a dynamic parameter and defaults to 1 when shared server is used.
Max_shared_servers: This is a static parameter and defaults to 20 or 2*shared_servers.
The steps we followed were:
SQL> alter system set circuits = 10000 scope=spfile;
System altered.
SQL> alter system set max_shared_servers=200 scope=spfile;
System altered.
SQL> alter system set shared_servers=50 scope=spfile;
System altered.
SQL> create pfile from spfile;
File created.
We have inserted the following line to the pfile created.
dispatchers='(protocol=tcp)(dispatchers=5)'
Then shutdown, create spfile from pfile and startup the instance.
Thursday, January 1, 2009
Statspack Installation - Steps
Detailed Steps to Configure Statspack!
Definition made simple: -
The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters.
Definition made simple: -
The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters.
Space Requirements for Statspack!
Approximately 64MB is required to install Statspack.
If you install the package in a dictionary-managed tablespace, then you should monitor the space used by the objects created and, if required, adjust the storage clauses of the segments.
*** If you install the package in a locally managed tablespace, then storage clauses are not required, because the storage characteristics are automatically managed.
Start SQL*Plus.
Run the SPCREATE.SQL script.
SQL>@%ORACLE_HOME%\rdbms\admin\spcreate
Once you run this script it will prompt you enter password for perfstat user.
You need to enter default tablespace “Which Oracle recommends ‘TOOLS’
And its asks you to enter Temporary tablespace and that is your “TEMP” and within few minutes your Statspack ready to use.
You need to enter default tablespace “Which Oracle recommends ‘TOOLS’
And its asks you to enter Temporary tablespace and that is your “TEMP” and within few minutes your Statspack ready to use.
*** Once I learn using Statspack I get back with the analysis report.
Statspack Installation - Steps
Detailed Steps to Configure Statspack!
Definition made simple: -
The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters.
Definition made simple: -
The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters.
Space Requirements for Statspack!
Approximately 64MB is required to install Statspack.
If you install the package in a dictionary-managed tablespace, then you should monitor the space used by the objects created and, if required, adjust the storage clauses of the segments.
*** If you install the package in a locally managed tablespace, then storage clauses are not required, because the storage characteristics are automatically managed.
Start SQL*Plus.
Run the SPCREATE.SQL script.
SQL>@%ORACLE_HOME%\rdbms\admin\spcreate
Once you run this script it will prompt you enter password for perfstat user.
You need to enter default tablespace “Which Oracle recommends ‘TOOLS’
And its asks you to enter Temporary tablespace and that is your “TEMP” and within few minutes your Statspack ready to use.
You need to enter default tablespace “Which Oracle recommends ‘TOOLS’
And its asks you to enter Temporary tablespace and that is your “TEMP” and within few minutes your Statspack ready to use.
*** Once I learn using Statspack I get back with the analysis report.
Tuesday, December 23, 2008
Create Database Manually - Windows
Step to Create Database Manually
1. Create the directory
adump,bdump,cdump,dpump,pfile,udump,archive, flash_recovery_area under C:\oracle\product\10.2.0\admin folder...
Create testdb directory under C:\oracle\product\10.2.0\oradata\ folder
2. Go Cmd Prompt.
C:\>set oracle_sid=testdb
3 --Create Password File
C:\>orapwd file=C:\oracle\product\10.2.0\db_1\database\pwdtestdb.ora password=khaleel entries=5
4 --- Start Oracle Services
C:\>oradim.exe -new -sid testdb
C:\>oradim.exe -edit -sid testdb -startmode auto -srvcstart system
C:\>sqlplus /nolog
sql>conn / as sysdba
connected
Before running below script check database is shutdown
sql>startup nomount pfile=C:\oracle\product\10.2.0\admin\testdb\pfile\inittestdb.ora
5. --- database creation scripts.
CREATE DATABASE testdb
LOGFILE GROUP 1 ('C:\oracle\product\10.2.0\oradata\testdb\testredo01.log') SIZE 100M REUSE,
GROUP 2 ('C:\oracle\product\10.2.0\oradata\testdb\testredo02.log') SIZE 100M REUSE,
GROUP 3 ('C:\oracle\product\10.2.0\oradata\testdb\testredo03.log') SIZE 100M REUSE
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
ARCHIVELOG
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
DATAFILE 'C:\oracle\product\10.2.0\oradata\testdb\testsystem01.dbf' SIZE 200M REUSE
SYSAUX DATAFILE 'C:\oracle\product\10.2.0\oradata\testdb\testsysaux01.dbf' SIZE 500M
TEMP TABLESPACE TEMP_TESTDB datafile 'C:\oracle\product\10.2.0\oradata\testdb\temptestdb01.dbf' size 500M
USERS TABLESPACE USERS_TESTDB datafile 'C:\oracle\product\10.2.0\oradata\testdb\userstestdb01.dbf' size 500M
UNDO TABLESPACE UNDO_TESTDB datafile 'C:\oracle\product\10.2.0\oradata\testdb\undotest01.dbf' size 500M;
6 --- run after database create script
sql>@C:\oracle\product\10.2.0\db_1\rdbms\admin\catalog.sql;
sql>@C:\oracle\product\10.2.0\db_1\rdbms\admin\catproc.sql;
sql>connect SYSTEM/&&systemPassword
sql>@C:\oracle\product\10.2.0\db_1\sqlplus\admin\pupbld.sql;
sql>conn / as sysdba
sql>shutdown
sql>startup pfile=C:\oracle\product\10.2.0\admin\testdb\pfile\inittestdb.ora
1. Create the directory
adump,bdump,cdump,dpump,pfile,udump,archive, flash_recovery_area under C:\oracle\product\10.2.0\admin folder...
Create testdb directory under C:\oracle\product\10.2.0\oradata\ folder
2. Go Cmd Prompt.
C:\>set oracle_sid=testdb
3 --Create Password File
C:\>orapwd file=C:\oracle\product\10.2.0\db_1\database\pwdtestdb.ora password=khaleel entries=5
4 --- Start Oracle Services
C:\>oradim.exe -new -sid testdb
C:\>oradim.exe -edit -sid testdb -startmode auto -srvcstart system
C:\>sqlplus /nolog
sql>conn / as sysdba
connected
Before running below script check database is shutdown
sql>startup nomount pfile=C:\oracle\product\10.2.0\admin\testdb\pfile\inittestdb.ora
5. --- database creation scripts.
CREATE DATABASE testdb
LOGFILE GROUP 1 ('C:\oracle\product\10.2.0\oradata\testdb\testredo01.log') SIZE 100M REUSE,
GROUP 2 ('C:\oracle\product\10.2.0\oradata\testdb\testredo02.log') SIZE 100M REUSE,
GROUP 3 ('C:\oracle\product\10.2.0\oradata\testdb\testredo03.log') SIZE 100M REUSE
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
ARCHIVELOG
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
DATAFILE 'C:\oracle\product\10.2.0\oradata\testdb\testsystem01.dbf' SIZE 200M REUSE
SYSAUX DATAFILE 'C:\oracle\product\10.2.0\oradata\testdb\testsysaux01.dbf' SIZE 500M
TEMP TABLESPACE TEMP_TESTDB datafile 'C:\oracle\product\10.2.0\oradata\testdb\temptestdb01.dbf' size 500M
USERS TABLESPACE USERS_TESTDB datafile 'C:\oracle\product\10.2.0\oradata\testdb\userstestdb01.dbf' size 500M
UNDO TABLESPACE UNDO_TESTDB datafile 'C:\oracle\product\10.2.0\oradata\testdb\undotest01.dbf' size 500M;
6 --- run after database create script
sql>@C:\oracle\product\10.2.0\db_1\rdbms\admin\catalog.sql;
sql>@C:\oracle\product\10.2.0\db_1\rdbms\admin\catproc.sql;
sql>connect SYSTEM/&&systemPassword
sql>@C:\oracle\product\10.2.0\db_1\sqlplus\admin\pupbld.sql;
sql>conn / as sysdba
sql>shutdown
sql>startup pfile=C:\oracle\product\10.2.0\admin\testdb\pfile\inittestdb.ora
Create Database Manually - Windows
Step to Create Database Manually
1. Create the directory
adump,bdump,cdump,dpump,pfile,udump,archive, flash_recovery_area under C:\oracle\product\10.2.0\admin folder...
Create testdb directory under C:\oracle\product\10.2.0\oradata\ folder
2. Go Cmd Prompt.
C:\>set oracle_sid=testdb
3 --Create Password File
C:\>orapwd file=C:\oracle\product\10.2.0\db_1\database\pwdtestdb.ora password=khaleel entries=5
4 --- Start Oracle Services
C:\>oradim.exe -new -sid testdb
C:\>oradim.exe -edit -sid testdb -startmode auto -srvcstart system
C:\>sqlplus /nolog
sql>conn / as sysdba
connected
Before running below script check database is shutdown
sql>startup nomount pfile=C:\oracle\product\10.2.0\admin\testdb\pfile\inittestdb.ora
5. --- database creation scripts.
CREATE DATABASE testdb
LOGFILE GROUP 1 ('C:\oracle\product\10.2.0\oradata\testdb\testredo01.log') SIZE 100M REUSE,
GROUP 2 ('C:\oracle\product\10.2.0\oradata\testdb\testredo02.log') SIZE 100M REUSE,
GROUP 3 ('C:\oracle\product\10.2.0\oradata\testdb\testredo03.log') SIZE 100M REUSE
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
ARCHIVELOG
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
DATAFILE 'C:\oracle\product\10.2.0\oradata\testdb\testsystem01.dbf' SIZE 200M REUSE
SYSAUX DATAFILE 'C:\oracle\product\10.2.0\oradata\testdb\testsysaux01.dbf' SIZE 500M
TEMP TABLESPACE TEMP_TESTDB datafile 'C:\oracle\product\10.2.0\oradata\testdb\temptestdb01.dbf' size 500M
USERS TABLESPACE USERS_TESTDB datafile 'C:\oracle\product\10.2.0\oradata\testdb\userstestdb01.dbf' size 500M
UNDO TABLESPACE UNDO_TESTDB datafile 'C:\oracle\product\10.2.0\oradata\testdb\undotest01.dbf' size 500M;
6 --- run after database create script
sql>@C:\oracle\product\10.2.0\db_1\rdbms\admin\catalog.sql;
sql>@C:\oracle\product\10.2.0\db_1\rdbms\admin\catproc.sql;
sql>connect SYSTEM/&&systemPassword
sql>@C:\oracle\product\10.2.0\db_1\sqlplus\admin\pupbld.sql;
sql>conn / as sysdba
sql>shutdown
sql>startup pfile=C:\oracle\product\10.2.0\admin\testdb\pfile\inittestdb.ora
1. Create the directory
adump,bdump,cdump,dpump,pfile,udump,archive, flash_recovery_area under C:\oracle\product\10.2.0\admin folder...
Create testdb directory under C:\oracle\product\10.2.0\oradata\ folder
2. Go Cmd Prompt.
C:\>set oracle_sid=testdb
3 --Create Password File
C:\>orapwd file=C:\oracle\product\10.2.0\db_1\database\pwdtestdb.ora password=khaleel entries=5
4 --- Start Oracle Services
C:\>oradim.exe -new -sid testdb
C:\>oradim.exe -edit -sid testdb -startmode auto -srvcstart system
C:\>sqlplus /nolog
sql>conn / as sysdba
connected
Before running below script check database is shutdown
sql>startup nomount pfile=C:\oracle\product\10.2.0\admin\testdb\pfile\inittestdb.ora
5. --- database creation scripts.
CREATE DATABASE testdb
LOGFILE GROUP 1 ('C:\oracle\product\10.2.0\oradata\testdb\testredo01.log') SIZE 100M REUSE,
GROUP 2 ('C:\oracle\product\10.2.0\oradata\testdb\testredo02.log') SIZE 100M REUSE,
GROUP 3 ('C:\oracle\product\10.2.0\oradata\testdb\testredo03.log') SIZE 100M REUSE
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
ARCHIVELOG
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
DATAFILE 'C:\oracle\product\10.2.0\oradata\testdb\testsystem01.dbf' SIZE 200M REUSE
SYSAUX DATAFILE 'C:\oracle\product\10.2.0\oradata\testdb\testsysaux01.dbf' SIZE 500M
TEMP TABLESPACE TEMP_TESTDB datafile 'C:\oracle\product\10.2.0\oradata\testdb\temptestdb01.dbf' size 500M
USERS TABLESPACE USERS_TESTDB datafile 'C:\oracle\product\10.2.0\oradata\testdb\userstestdb01.dbf' size 500M
UNDO TABLESPACE UNDO_TESTDB datafile 'C:\oracle\product\10.2.0\oradata\testdb\undotest01.dbf' size 500M;
6 --- run after database create script
sql>@C:\oracle\product\10.2.0\db_1\rdbms\admin\catalog.sql;
sql>@C:\oracle\product\10.2.0\db_1\rdbms\admin\catproc.sql;
sql>connect SYSTEM/&&systemPassword
sql>@C:\oracle\product\10.2.0\db_1\sqlplus\admin\pupbld.sql;
sql>conn / as sysdba
sql>shutdown
sql>startup pfile=C:\oracle\product\10.2.0\admin\testdb\pfile\inittestdb.ora
Subscribe to:
Posts (Atom)
