1. What Is Oracle Database 10g Express Edition?
Based on Oracle Web site: Oracle Database 10g Express Edition (Oracle Database XE) is an entry-level, small-footprint database based on the Oracle Database 10g Release 2 code base that's free to develop, deploy, and distribute; fast to download; and simple to administer. Oracle Database XE is a great starter database for:
◉ Developers working on PHP, Java, .NET, and Open Source applications
◉ DBAs who need a free, starter database for training and deployment
◉ Independent Software Vendors (ISVs) and hardware vendors who want a starter database to distribute free of charge
◉ Educational institutions and students who need a free database for their curriculum
2. What Are the Limitations of Oracle Database 10g XE?
Oracle Database XE is free for runtime usage with the following limitations:
◉ Supports up to 4GB of user data (in addition to Oracle system data)
◉ Single instance only of Oracle Database XE on any server
◉ May be installed on a multiple CPU server, but only executes on one processor in any server
◉ May be installed on a server with any amount of memory, but will only use up to 1GB RAM of available memory
3. What Operating Systems Are Supported by Oracle Database 10g XE?
Oracle Database 10g Express Edition is available for two types of operating Systems:
◉ Linux x86 - Debian, Mandriva, Novell, Red Hat and Ubuntu
◉ Microsoft Windows
4. How To Download Oracle Database 10g XE?
If you want to download a copy of Oracle Database 10g Express Edition, visit http://www.oracle.com/technology/software/products/database/xe/.
If you are using Windows systems, there are downloads available for you:
◉ Oracle Database 10g Express Edition (Western European) - Single-byte LATIN1 database for Western European language storage, with the Database Homepage user interface in English only.
◉ Oracle Database 10g Express Edition (Universal) - Multi-byte Unicode database for all language deployment, with the Database Homepage user interface available in the following languages: Brazilian Portuguese, Chinese (Simplified and Traditional), English, French, German, Italian, Japanese, Korean and Spanish.
◉ Oracle Database 10g Express Client
You need to download the universal edition, OracleXEUniv.exe, (216,933,372 bytes) and client package, OracleXEClient.exe (30,943,220 bytes).
5. How To Install Oracle Database 10g XE?
To install 10g universal edition, double click, OracleXEUniv.exe, the install wizard starts. It will guide you to finish the installation process. You should take notes about:
◉ The SYSTEM password you selecte: fyicenter.
◉ Database server port: 1521.
◉ Database HTTP port: 8080.
◉ MS Transaction Server port: 2030.
◉ The directory where 10g XE is installed: \oraclexe\
◉ Hard disk space taken: 1655MB.
6. How To Check Your Oracle Database 10g XE Installation?
If you want to check your fresh installation of 10g Express Edition without using any special client programs, you can use a Web browser with this address, http://localhost:8080/apex/.
You will see the login page. Enter SYSTEM as the user name, and the password (fyicenter), you selected during the installation to log into the server.
Visit different areas on your 10g XE server home page to make sure your server is running OK.
You can also get to your 10g XE server home page by going through the start menu. Select All Programs, then Oracle Database 10g Express Edition, and then Go To Database Home Page.
7. How To Shutdown Your 10g XE Server?
If you want to shutdown your 10g Express Edition server, go to the Services manager in the control panel. You will a service called OracleServiceXE, which represents your 10g Express Edition server.
Select OracleServiceXE, and use the right mouse click to stop this service. This will shutdown your 10g Express Edition server.
You can also shutdown your 10g XE server through the start menu. Select All Programs, then Oracle Database 10g Express Edition, and then Stop Database.
8. How To Start Your 10g XE Server?
Go to the Start menu, select All Programs, Oracle Database 10g Express Edition, and Start Database.
9. How Much Memory Your 10g XE Server Is Using?
Your 10g XE Server is using about 180MB of memory even there is no users on the server. The server memory usage is displayed on your server home page, if you log in as SYSTEM.
10. How To Start Your 10g XE Server from Command Line?
You can start your 10g XE server from command line by:
◉ Open a command line window.
◉ Change directory to \oraclexe\app\oracle\product\10.2.0\server\BIN\.
◉ Run StartDB.bat.
The batch file StartDB.bat contains:
net start OracleXETNSListener
net start OracleServiceXE
@oradim -startup -sid XE -starttype inst > nul 2>&1
11. How To Shutdown Your 10g XE Server from Command Line?
You can shutdown your 10g XE server from command line by:
◉ Open a command line window.
◉ Change directory to \oraclexe\app\oracle\product\10.2.0\server\BIN\.
◉ Run StopDB.bat.
The batch file StopDB.bat contains:
net stop OracleServiceXE
12. How To Unlock the Sample User Account?
Your 10g XE server comes with a sample database user account called HR. But this account is locked. You must unlock it before you can use it:
◉ Log into the server home page as SYSTEM.
◉ Click the Administration icon, and then click Database Users.
◉ Click the HR schema icon to display the user information for HR.
◉ Enter a new password (hr) for HR, and change the status to Unlocked.
◉ Click Alter User to save the changes.
Now user account HR is ready to use.
13. How To Change System Global Area (SGA)?
Your 10g XE server has a default setting for System Global Area (SGA) of 140MB. The SGA size can be changed to a new value depending on how many concurrent sessions connecting to your server. If you are running this server just for yourself to improve your DBA skill, you should change the SGA size to 32MB by:
◉ Log into the server home page as SYSTEM.
◉ Go to Administration, then Memory.
◉ Click Configure SGA.
◉ Enter the new memory size: 32
◉ Click Apply Changes to save the changes.
◉ Re-start your server.
14. How To Change Program Global Area (PGA)?
Your 10g XE server has a default setting for Program Global Area (PGA) of 40MB. The PGA size can be changed to a new value depending on how much data a single session should be allocated. If you think your session will be short with a small amount of data, you should change the PGA size to 16MB by:
◉ Log into the server home page as SYSTEM.
◉ Go to Administration, then Memory.
◉ Click Configure PGA.
◉ Enter the new memory size: 16
◉ Click Apply Changes to save the changes.
◉ Re-start your server.
15. What Happens If You Set the SGA Too Low?
Let's you made a mistake and changed to SGA to 16MB from the SYSTEM admin home page. When you run the batch file StartDB.bat, it will return a message saying server stated. However, if you try to connect to your server home page: http://localhost:8080/apex/, you will get no response. Why? Your server is running, but the default instance XE was not started.
If you go the Control Panel and Services, you will see service OracleServiceXE is listed not in the running status.
16. What To Do If the StartDB.bat Failed to Start the XE Instance?
If StartDB.bat failed to start the XE instance, you need to try to start the instance with other approaches to get detail error messages on why the instance can not be started.
One good approach to start the default instance is to use SQL*Plus. Here is how to use SQL*Plus to start the default instance in a command window:
>cd (OracleXE home directory)
>.\bin\startdb
>.\bin\sqlplus
Enter user-name: SYSTEM
Enter password: fyicenter
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
The first "cd" is to move the current directory the 10g XE home directory. The second command ".\bin\startdb" is to make sure the TNS listener is running. The third command ".\bin\sqlplus" launches SQL*Plus. The error message "ORA-27101" tells you that there is a memory problem with the default instance.
So you can not use the normal login process to the server without a good instance. See other tips on how to log into a server without any instance.
17. How To Login to the Server without an Instance?
If your default instance is in trouble, and you can not use the normal login process to reach the server, you can use a special login to log into the server without any instance. Here is how to use SQL*Plus to log in as as a system BDA:
>cd (OracleXE home directory)
>.\bin\startdb
>.\bin\sqlplus
Enter user-name: SYSTEM/fyicenter AS SYSDBA
Connected to an idle instance
SQL> show instance
instance "local"
The trick is to put user name, password and login options in a single string as the user name. "AS SYSDBA" tells the server to not start any instance, and connect the session the idle instance.
Log in as SYSDBA is very useful for performing DBA tasks.
18. How To Use "startup" Command to Start Default Instance?
If you logged in to the server as a SYSDBA, you start the default instance with the "startup" command. Here is how to start the default instance in SQL*Plus in SYSDBA mode:
>.\bin\sqlplus
Enter user-name: SYSTEM/fyicenter AS SYSDBA
Connected to an idle instance
SQL> show instance
instance "local"
SQL> startup
ORA-00821: Specified value of sga_target 16M is too small,
needs to be at least 20M
Now the server is telling you more details about the memory problem on your default instance: your SGA setting of 16MB is too small. It must be increased to at least 20MB.
19. Where Are the Settings Stored for Each Instance?
Settings for each instance are stored in a file called Server Parameter File (SPFile). Oracle supports two types of parameter files, Text type, and Binary type. parameter files should be located in $ORACLE_HOME\database directory. A parameter file should be named like "init$SID.ora", where $SID is the instance name.
20. What To Do If the Binary SPFile Is Wrong for the Default Instance?
Let's say the SPFile for the default instance is a binary file, and some settings are wrong in the SPFile, like SGA setting is bellow 20MB, how do you change a setting in the binary file? This seems to be a hard task, because the binary SPFile is not allowed to be edited manually. It needs to be updated by the server with instance started. But you can not start the instance because the SPFile has a wrong setting.
One way to solve the problem is to stop using the binary SPFile, and use a text version of the a parameter file to start the instance. Here is an example of how to use the backup copy (text version) of the parameter file for the default instance to start the instance:
>.\bin\sqlplus
Enter user-name: SYSTEM/fyicenter AS SYSDBA
Connected to an idle instance
SQL> startup
PFILE=$ORACLE_HOME\config\scripts\initXETemp.ora;
ORACLE instance started.
Total System Global Area 146800640 bytes
Fixed Size 1286220 bytes
Variable Size 58724276 bytes
Database Buffers 83886080 bytes
Redo Buffers 2904064 bytes
Database mounted.
Database opened.
As you can see, 10g XE is distributed with a backup copy of the parameter file for the default instance XE. The "startup" can take an option called PFILE to let you use a SPFILE from any location. The default instance is running correctly now.
21. How To Check the Server Version?
Oracle server sersion information is stored in a table called: PRODUCT_COMPONENT_VERSION. You can use a simple SELECT statement to view the version information like this:
>.\bin\sqlplus
Enter user-name: SYSTEM/fyicenter AS SYSDBA
Connected to an idle instance
SQL> COL PRODUCT FORMAT A35
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;
PRODUCT VERSION STATUS
----------------------------------- ----------- ----------
NLSRTL 10.2.0.1.0 Production
Oracle Database 10g Express Edition 10.2.0.1.0 Product
PL/SQL 10.2.0.1.0 Production
TNS for 32-bit Windows: 10.2.0.1.0 Production
Based on Oracle Web site: Oracle Database 10g Express Edition (Oracle Database XE) is an entry-level, small-footprint database based on the Oracle Database 10g Release 2 code base that's free to develop, deploy, and distribute; fast to download; and simple to administer. Oracle Database XE is a great starter database for:
◉ Developers working on PHP, Java, .NET, and Open Source applications
◉ DBAs who need a free, starter database for training and deployment
◉ Independent Software Vendors (ISVs) and hardware vendors who want a starter database to distribute free of charge
◉ Educational institutions and students who need a free database for their curriculum
2. What Are the Limitations of Oracle Database 10g XE?
Oracle Database XE is free for runtime usage with the following limitations:
◉ Supports up to 4GB of user data (in addition to Oracle system data)
◉ Single instance only of Oracle Database XE on any server
◉ May be installed on a multiple CPU server, but only executes on one processor in any server
◉ May be installed on a server with any amount of memory, but will only use up to 1GB RAM of available memory
3. What Operating Systems Are Supported by Oracle Database 10g XE?
Oracle Database 10g Express Edition is available for two types of operating Systems:
◉ Linux x86 - Debian, Mandriva, Novell, Red Hat and Ubuntu
◉ Microsoft Windows
4. How To Download Oracle Database 10g XE?
If you want to download a copy of Oracle Database 10g Express Edition, visit http://www.oracle.com/technology/software/products/database/xe/.
If you are using Windows systems, there are downloads available for you:
◉ Oracle Database 10g Express Edition (Western European) - Single-byte LATIN1 database for Western European language storage, with the Database Homepage user interface in English only.
◉ Oracle Database 10g Express Edition (Universal) - Multi-byte Unicode database for all language deployment, with the Database Homepage user interface available in the following languages: Brazilian Portuguese, Chinese (Simplified and Traditional), English, French, German, Italian, Japanese, Korean and Spanish.
◉ Oracle Database 10g Express Client
You need to download the universal edition, OracleXEUniv.exe, (216,933,372 bytes) and client package, OracleXEClient.exe (30,943,220 bytes).
5. How To Install Oracle Database 10g XE?
To install 10g universal edition, double click, OracleXEUniv.exe, the install wizard starts. It will guide you to finish the installation process. You should take notes about:
◉ The SYSTEM password you selecte: fyicenter.
◉ Database server port: 1521.
◉ Database HTTP port: 8080.
◉ MS Transaction Server port: 2030.
◉ The directory where 10g XE is installed: \oraclexe\
◉ Hard disk space taken: 1655MB.
6. How To Check Your Oracle Database 10g XE Installation?
If you want to check your fresh installation of 10g Express Edition without using any special client programs, you can use a Web browser with this address, http://localhost:8080/apex/.
You will see the login page. Enter SYSTEM as the user name, and the password (fyicenter), you selected during the installation to log into the server.
Visit different areas on your 10g XE server home page to make sure your server is running OK.
You can also get to your 10g XE server home page by going through the start menu. Select All Programs, then Oracle Database 10g Express Edition, and then Go To Database Home Page.
7. How To Shutdown Your 10g XE Server?
If you want to shutdown your 10g Express Edition server, go to the Services manager in the control panel. You will a service called OracleServiceXE, which represents your 10g Express Edition server.
Select OracleServiceXE, and use the right mouse click to stop this service. This will shutdown your 10g Express Edition server.
You can also shutdown your 10g XE server through the start menu. Select All Programs, then Oracle Database 10g Express Edition, and then Stop Database.
8. How To Start Your 10g XE Server?
Go to the Start menu, select All Programs, Oracle Database 10g Express Edition, and Start Database.
9. How Much Memory Your 10g XE Server Is Using?
Your 10g XE Server is using about 180MB of memory even there is no users on the server. The server memory usage is displayed on your server home page, if you log in as SYSTEM.
10. How To Start Your 10g XE Server from Command Line?
You can start your 10g XE server from command line by:
◉ Open a command line window.
◉ Change directory to \oraclexe\app\oracle\product\10.2.0\server\BIN\.
◉ Run StartDB.bat.
The batch file StartDB.bat contains:
net start OracleXETNSListener
net start OracleServiceXE
@oradim -startup -sid XE -starttype inst > nul 2>&1
11. How To Shutdown Your 10g XE Server from Command Line?
You can shutdown your 10g XE server from command line by:
◉ Open a command line window.
◉ Change directory to \oraclexe\app\oracle\product\10.2.0\server\BIN\.
◉ Run StopDB.bat.
The batch file StopDB.bat contains:
net stop OracleServiceXE
12. How To Unlock the Sample User Account?
Your 10g XE server comes with a sample database user account called HR. But this account is locked. You must unlock it before you can use it:
◉ Log into the server home page as SYSTEM.
◉ Click the Administration icon, and then click Database Users.
◉ Click the HR schema icon to display the user information for HR.
◉ Enter a new password (hr) for HR, and change the status to Unlocked.
◉ Click Alter User to save the changes.
Now user account HR is ready to use.
13. How To Change System Global Area (SGA)?
Your 10g XE server has a default setting for System Global Area (SGA) of 140MB. The SGA size can be changed to a new value depending on how many concurrent sessions connecting to your server. If you are running this server just for yourself to improve your DBA skill, you should change the SGA size to 32MB by:
◉ Log into the server home page as SYSTEM.
◉ Go to Administration, then Memory.
◉ Click Configure SGA.
◉ Enter the new memory size: 32
◉ Click Apply Changes to save the changes.
◉ Re-start your server.
14. How To Change Program Global Area (PGA)?
Your 10g XE server has a default setting for Program Global Area (PGA) of 40MB. The PGA size can be changed to a new value depending on how much data a single session should be allocated. If you think your session will be short with a small amount of data, you should change the PGA size to 16MB by:
◉ Log into the server home page as SYSTEM.
◉ Go to Administration, then Memory.
◉ Click Configure PGA.
◉ Enter the new memory size: 16
◉ Click Apply Changes to save the changes.
◉ Re-start your server.
15. What Happens If You Set the SGA Too Low?
Let's you made a mistake and changed to SGA to 16MB from the SYSTEM admin home page. When you run the batch file StartDB.bat, it will return a message saying server stated. However, if you try to connect to your server home page: http://localhost:8080/apex/, you will get no response. Why? Your server is running, but the default instance XE was not started.
If you go the Control Panel and Services, you will see service OracleServiceXE is listed not in the running status.
16. What To Do If the StartDB.bat Failed to Start the XE Instance?
If StartDB.bat failed to start the XE instance, you need to try to start the instance with other approaches to get detail error messages on why the instance can not be started.
One good approach to start the default instance is to use SQL*Plus. Here is how to use SQL*Plus to start the default instance in a command window:
>cd (OracleXE home directory)
>.\bin\startdb
>.\bin\sqlplus
Enter user-name: SYSTEM
Enter password: fyicenter
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
The first "cd" is to move the current directory the 10g XE home directory. The second command ".\bin\startdb" is to make sure the TNS listener is running. The third command ".\bin\sqlplus" launches SQL*Plus. The error message "ORA-27101" tells you that there is a memory problem with the default instance.
So you can not use the normal login process to the server without a good instance. See other tips on how to log into a server without any instance.
17. How To Login to the Server without an Instance?
If your default instance is in trouble, and you can not use the normal login process to reach the server, you can use a special login to log into the server without any instance. Here is how to use SQL*Plus to log in as as a system BDA:
>cd (OracleXE home directory)
>.\bin\startdb
>.\bin\sqlplus
Enter user-name: SYSTEM/fyicenter AS SYSDBA
Connected to an idle instance
SQL> show instance
instance "local"
The trick is to put user name, password and login options in a single string as the user name. "AS SYSDBA" tells the server to not start any instance, and connect the session the idle instance.
Log in as SYSDBA is very useful for performing DBA tasks.
18. How To Use "startup" Command to Start Default Instance?
If you logged in to the server as a SYSDBA, you start the default instance with the "startup" command. Here is how to start the default instance in SQL*Plus in SYSDBA mode:
>.\bin\sqlplus
Enter user-name: SYSTEM/fyicenter AS SYSDBA
Connected to an idle instance
SQL> show instance
instance "local"
SQL> startup
ORA-00821: Specified value of sga_target 16M is too small,
needs to be at least 20M
Now the server is telling you more details about the memory problem on your default instance: your SGA setting of 16MB is too small. It must be increased to at least 20MB.
19. Where Are the Settings Stored for Each Instance?
Settings for each instance are stored in a file called Server Parameter File (SPFile). Oracle supports two types of parameter files, Text type, and Binary type. parameter files should be located in $ORACLE_HOME\database directory. A parameter file should be named like "init$SID.ora", where $SID is the instance name.
20. What To Do If the Binary SPFile Is Wrong for the Default Instance?
Let's say the SPFile for the default instance is a binary file, and some settings are wrong in the SPFile, like SGA setting is bellow 20MB, how do you change a setting in the binary file? This seems to be a hard task, because the binary SPFile is not allowed to be edited manually. It needs to be updated by the server with instance started. But you can not start the instance because the SPFile has a wrong setting.
One way to solve the problem is to stop using the binary SPFile, and use a text version of the a parameter file to start the instance. Here is an example of how to use the backup copy (text version) of the parameter file for the default instance to start the instance:
>.\bin\sqlplus
Enter user-name: SYSTEM/fyicenter AS SYSDBA
Connected to an idle instance
SQL> startup
PFILE=$ORACLE_HOME\config\scripts\initXETemp.ora;
ORACLE instance started.
Total System Global Area 146800640 bytes
Fixed Size 1286220 bytes
Variable Size 58724276 bytes
Database Buffers 83886080 bytes
Redo Buffers 2904064 bytes
Database mounted.
Database opened.
As you can see, 10g XE is distributed with a backup copy of the parameter file for the default instance XE. The "startup" can take an option called PFILE to let you use a SPFILE from any location. The default instance is running correctly now.
21. How To Check the Server Version?
Oracle server sersion information is stored in a table called: PRODUCT_COMPONENT_VERSION. You can use a simple SELECT statement to view the version information like this:
>.\bin\sqlplus
Enter user-name: SYSTEM/fyicenter AS SYSDBA
Connected to an idle instance
SQL> COL PRODUCT FORMAT A35
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;
PRODUCT VERSION STATUS
----------------------------------- ----------- ----------
NLSRTL 10.2.0.1.0 Production
Oracle Database 10g Express Edition 10.2.0.1.0 Product
PL/SQL 10.2.0.1.0 Production
TNS for 32-bit Windows: 10.2.0.1.0 Production
Posts
0 comments:
Post a Comment