May the dawning of this New Year, fill your heart with new hopes, open up new horizons and bring for you promises of brighter tomorrows. May you have a great New Year - 2008.
Monday, December 31, 2007
Sunday, December 30, 2007
Some of the Oracle Database 11g New Features
Oracle Database 11g Release 1 (11.1) New Features
=================================================
Case Sensitive Passwords
Beginning with Oracle Database 11g Release 1, database passwords are case sensitive.
You can disable this features by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE.
=======================================
Readonly Tables
To place a table in read-only mode, you must have the ALTER TABLE privilege on thetable or the ALTER ANY
TABLE privilege. In addition, the COMPATIBILE initializationparameter must be set to 11.1.0 or greater.
The following example places the EMP table in read-only mode:
ALTER TABLE EMP READ ONLY;
The following example returns the table to read/write mode:
ALTER TABLE EMP READ WRITE;
=========================================
Invisible Indexes
An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Making an index invisible is an alternative to making it unusable or dropping it.
Using invisible indexes, you can do the following:
Test the removal of an index before dropping it.
Altering Indexes
Use temporary index structures for certain operations or modules of an application without affecting the overall application.
To create an invisible index, use the SQL statement CREATE INDEX with theINVISIBLE clause. The following statement creates an invisible index named IND_EMP_DEPT for the DEPT column of the EMP table:
CREATE INDEX ind_emp_dept ON emp(dept)INVISIBLE;
To make an invisible index visible, issue this statement:
ALTER INDEX ind_emp_dept VISIBLE;
To make a visible index invisible, issue this statement:
ALTER INDEX ind_emp_dept INVISIBLE;
To check whether an index is visible or invisible, query the dictionary views
USER[ALL][DBA]_INDEXES
For example, to determine if above index is invisible, issue the following query:
SELECT INDEX_NAME, VISIBILITY FROM
USER_INDEXES WHERE INDEX_NAME = 'IND_EMP_DEPT';
INDEX_NAME VISIBILITY
------------- ----------
IND_EMP_DEPT VISIBLE
===========================================
Automatic Stats Collection on Tables
As you know that you can enable DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement. But, starting with Oracle Database 11g, the MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.
Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the table since the last time statistics were gathered. Information abouthow many rows are affected is maintained in the SGA, until periodically (about everythree hours) SMON incorporates the data into the data dictionary.
This data dictionary information is made visible through the DBA_TAB_MODIFICATIONS, ALL_TAB_MODIFICATIONS, or USER_TAB_MODIFICATIONS views. The database uses these
views to identify tables with stale statistics.
=================================================
Virtual columns
Tables can now include virtual columns. The value of a virtual column in a row is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions. In some cases, a virtual column eliminates the need to create a separate view. You can create an index on a virtual column, and you can use a virtual column as a partition or subpartition key.
==============================================
Simplified and improved automatic memory management
You can now set a single initialization parameter (MEMORY_TARGET) to indicate the total amount of memory that is to be allocated to the database (the SGA and instance PGA). The system then automatically and dynamically tunes all SGA and PGA components for optimal performance. You can still designate minimum sizes individually for the SGA and instance PGA.
===============================================
Database resident connection pooling
Database resident connection pooling (DRCP) provides a connection pool in the database server for typical Web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it. DRCP pools "dedicated" servers, which are the equivalent of a server foreground process and a database session combined. DRCP enables sharing of
database connections across middle-tier processes on the same middle-tier host and across middle-tier hosts. This results in significant reduction in database resources needed to support a large number of client connections, thereby boosting the scalability of both middle-tier and database tiers.
==================================================
Tablespace-level encryption
You can encrypt any permanent tablespace to protect sensitive data. Tablespace encryption is completely transparent to your applications. When you encrypt a tablespace, all tablespace blocks are encrypted.
All segment types are supported for encryption, including tables, clusters, indexes, LOBs, table and index partitions, and so on.
==========================================================
Result cache in the system global area
Results of queries and query fragments can be cached in memory in the result cache. The database can then use cached results to answer future executions of these queries and query fragments. Because retrieving results from the result cache is faster than rerunning a query, frequently run queries experience a significant performance improvement when their results are cached.
The result cache occupies memory in the shared pool.
=============================================================
Enhanced online index creation and rebuild
Online index creation and rebuild prior to this release required a DML-blocking lock at the beginning and at the end of the rebuild for a short period of time. This lock could delay other DML statements and therefore cause a performance spike. This lock is no longer required, making these online index operations fully transparent.
===============================================================
Ability to online redefine tables that have materialized view logs
Tables with materialized view logs can now be redefined online. Materialized view logs are now one of the dependent objects that can be copied to the interim table with the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS package procedure.
=====================================================================
Optimized ALTER TABLE...ADD COLUMN
For certain types of tables, when adding a column that has both a NOT NULL constraint and a default value, the database can optimize the resource usage and storage requirements for the operation. It does so by storing the default value for the new column as table metadata, avoiding the need to store the value in all existing records.
In addition, the following ADD COLUMN operations can now run concurrently with DML operations:
Add a NOT NULL column with a default value
Add a nullable column without a default value
Add a virtual column
==========================================================
=================================================
Case Sensitive Passwords
Beginning with Oracle Database 11g Release 1, database passwords are case sensitive.
You can disable this features by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE.
=======================================
Readonly Tables
To place a table in read-only mode, you must have the ALTER TABLE privilege on thetable or the ALTER ANY
TABLE privilege. In addition, the COMPATIBILE initializationparameter must be set to 11.1.0 or greater.
The following example places the EMP table in read-only mode:
ALTER TABLE EMP READ ONLY;
The following example returns the table to read/write mode:
ALTER TABLE EMP READ WRITE;
=========================================
Invisible Indexes
An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Making an index invisible is an alternative to making it unusable or dropping it.
Using invisible indexes, you can do the following:
Test the removal of an index before dropping it.
Altering Indexes
Use temporary index structures for certain operations or modules of an application without affecting the overall application.
To create an invisible index, use the SQL statement CREATE INDEX with theINVISIBLE clause. The following statement creates an invisible index named IND_EMP_DEPT for the DEPT column of the EMP table:
CREATE INDEX ind_emp_dept ON emp(dept)INVISIBLE;
To make an invisible index visible, issue this statement:
ALTER INDEX ind_emp_dept VISIBLE;
To make a visible index invisible, issue this statement:
ALTER INDEX ind_emp_dept INVISIBLE;
To check whether an index is visible or invisible, query the dictionary views
USER[ALL][DBA]_INDEXES
For example, to determine if above index is invisible, issue the following query:
SELECT INDEX_NAME, VISIBILITY FROM
USER_INDEXES WHERE INDEX_NAME = 'IND_EMP_DEPT';
INDEX_NAME VISIBILITY
------------- ----------
IND_EMP_DEPT VISIBLE
===========================================
Automatic Stats Collection on Tables
As you know that you can enable DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement. But, starting with Oracle Database 11g, the MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.
Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the table since the last time statistics were gathered. Information abouthow many rows are affected is maintained in the SGA, until periodically (about everythree hours) SMON incorporates the data into the data dictionary.
This data dictionary information is made visible through the DBA_TAB_MODIFICATIONS, ALL_TAB_MODIFICATIONS, or USER_TAB_MODIFICATIONS views. The database uses these
views to identify tables with stale statistics.
=================================================
Virtual columns
Tables can now include virtual columns. The value of a virtual column in a row is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions. In some cases, a virtual column eliminates the need to create a separate view. You can create an index on a virtual column, and you can use a virtual column as a partition or subpartition key.
==============================================
Simplified and improved automatic memory management
You can now set a single initialization parameter (MEMORY_TARGET) to indicate the total amount of memory that is to be allocated to the database (the SGA and instance PGA). The system then automatically and dynamically tunes all SGA and PGA components for optimal performance. You can still designate minimum sizes individually for the SGA and instance PGA.
===============================================
Database resident connection pooling
Database resident connection pooling (DRCP) provides a connection pool in the database server for typical Web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it. DRCP pools "dedicated" servers, which are the equivalent of a server foreground process and a database session combined. DRCP enables sharing of
database connections across middle-tier processes on the same middle-tier host and across middle-tier hosts. This results in significant reduction in database resources needed to support a large number of client connections, thereby boosting the scalability of both middle-tier and database tiers.
==================================================
Tablespace-level encryption
You can encrypt any permanent tablespace to protect sensitive data. Tablespace encryption is completely transparent to your applications. When you encrypt a tablespace, all tablespace blocks are encrypted.
All segment types are supported for encryption, including tables, clusters, indexes, LOBs, table and index partitions, and so on.
==========================================================
Result cache in the system global area
Results of queries and query fragments can be cached in memory in the result cache. The database can then use cached results to answer future executions of these queries and query fragments. Because retrieving results from the result cache is faster than rerunning a query, frequently run queries experience a significant performance improvement when their results are cached.
The result cache occupies memory in the shared pool.
=============================================================
Enhanced online index creation and rebuild
Online index creation and rebuild prior to this release required a DML-blocking lock at the beginning and at the end of the rebuild for a short period of time. This lock could delay other DML statements and therefore cause a performance spike. This lock is no longer required, making these online index operations fully transparent.
===============================================================
Ability to online redefine tables that have materialized view logs
Tables with materialized view logs can now be redefined online. Materialized view logs are now one of the dependent objects that can be copied to the interim table with the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS package procedure.
=====================================================================
Optimized ALTER TABLE...ADD COLUMN
For certain types of tables, when adding a column that has both a NOT NULL constraint and a default value, the database can optimize the resource usage and storage requirements for the operation. It does so by storing the default value for the new column as table metadata, avoiding the need to store the value in all existing records.
In addition, the following ADD COLUMN operations can now run concurrently with DML operations:
Add a NOT NULL column with a default value
Add a nullable column without a default value
Add a virtual column
==========================================================
Friday, December 28, 2007
Oracle Express Editon
These are just some general points that might save you a bit of time and head scratching if you decide to give Oracle XE a go:
First, let's start with the good:
It's free to download and develop on
It's free to deploy (including distribution, as long as you make the other party aware of the licensing details)
It is the first really simple installation on either Windows or Linux that I have ever seen Oracle do
The included tools are superb and very modern and AJAXy
However, there are limitations:
--> Although you can install it on a multiple CPU machine, and it will run on one, it will only use one CPU on that machine.
--> It will only use a maximum 1 gigabyte of memory (again, it will run on machines with more memory; it just won't use it)
--> It has a database size limit of 4 gigabytes for user data (this is all available to the user; system data is counted outside of that 4 gig limit)
--> Only one instance of Oracle XE can run on any given computer.
You can download Oracle XE software from following link:
Download Link
First, let's start with the good:
It's free to download and develop on
It's free to deploy (including distribution, as long as you make the other party aware of the licensing details)
It is the first really simple installation on either Windows or Linux that I have ever seen Oracle do
The included tools are superb and very modern and AJAXy
However, there are limitations:
--> Although you can install it on a multiple CPU machine, and it will run on one, it will only use one CPU on that machine.
--> It will only use a maximum 1 gigabyte of memory (again, it will run on machines with more memory; it just won't use it)
--> It has a database size limit of 4 gigabytes for user data (this is all available to the user; system data is counted outside of that 4 gig limit)
--> Only one instance of Oracle XE can run on any given computer.
You can download Oracle XE software from following link:
Download Link
Locally vs. Dictionary Managed Tablespaces
When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks, called an extent, is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself. Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces.
SQL> select tablespace_name, extent_management, allocation_type from dba_tablespaces;
TABLESPACE_NAME EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM -- DICTIONARY -USER
UNDOTBS1 -- LOCAL -SYSTEM
TEMP -- LOCAL -UNIFORM
USERS -- LOCAL -SYSTEM
Dictionary Managed Tablespaces (DMT):
Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in "dictionary managed" mode. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes agianst these tables. As only one process can acquire the ST enque at a given time, this often lead to contention.
Execute the following statement to create a dictionary managed tablespace:
SQL> CREATE TABLESPACE tblspc1 DATAFILE '/oradata/tblspc1_01.dbf' SIZE 50M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);
Locally Managed Tablespaces (LMT):
Using LMT, each tablespace manages it's own free and used space within a bitmap structure stored in one of the tablespace's data files. Each bit corresponds to a database block or group of blocks. Execute one of the following statements to create a locally managed tablespace:
SQL> CREATE TABLESPACE tblspc2 DATAFILE '/oradata/tblspc2_01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
SQL> CREATE TABLESPACE tblspc3 DATAFILE '/oradata/tblspc3_01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
Note the difference between AUTOALLOCATE and UNIFORM SIZE:
AUTOALLOCATE specifies that extent sizes are system managed. Oracle will choose "optimal" next extent sizes starting with 64KB. As the segment grows larger extent sizes will increase to 1MB, 8MB, and eventually to 64MB. This is the recommended option for a low or unmanaged environment.
UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes (use K or M to specify the extent size in kilobytes or megabytes). The default size is 1M. The uniform extent size of a locally managed tablespace cannot be overridden when a schema object, such as a table or an index, is created.
Also not, if you specify, LOCAL, you cannot specify DEFAULT STORAGE, MINIMUM EXTENT or TEMPORARY.
Advantages of Locally Managed Tablespaces:
Eliminates the need for recursive SQL operations against the data dictionary (UET$ and FET$ tables)
Reduce contention on data dictionary tables (single ST enqueue)
Locally managed tablespaces eliminate the need to periodically coalesce free space (automatically tracks adjacent free space)
Changes to the extent bitmaps do not generate rollback information
Locally Managed SYSTEM Tablespace:
From Oracle9i release 9.2 one can change the SYSTEM tablespace to locally managed. Further, if you create a database with DBCA (Database Configuration Assistant), it will have a locally managed SYSTEM tablespace by default. The following restrictions apply:
No dictionary-managed tablespace in the database can be READ WRITE.
You cannot create new dictionary managed tablespaces
You cannot convert any dictionary managed tablespaces to local
Thus, it is best only to convert the SYSTEM tablespace to LMT after all other tablespaces are migrated to LMT.
Segment Space Management in LMT:
From Oracle 9i, one can not only have bitmap managed tablespaces, but also bitmap managed segments when setting Segment Space Management to AUTO for a tablespace. Look at this example:
SQL> CREATE TABLESPACE tblspc4 DATAFILE '/oradata/tblspc4_01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
Segment Space Management eliminates the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects. The Automatic Segment Space Management feature improves the performance of concurrent DML operations significantly since different parts of the bitmap can be used simultaneously eliminating serialization for free space lookups against the FREELSITS. This is of particular importance when using RAC, or if "buffer busy waits" are deteted.
Convert between LMT and DMT:
The DBMS_SPACE_ADMIN package allows DBAs to quickly and easily convert between LMT and DMT mode. Look at these examples:
SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('tblspc1');
PL/SQL procedure successfully completed.
SQL>exec dbms_space_admin.Tablespace_Migrate_FROM_Local('tblspc2');
PL/SQL procedure successfully completed.
SQL> select tablespace_name, extent_management, allocation_type from dba_tablespaces;
TABLESPACE_NAME EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM -- DICTIONARY -USER
UNDOTBS1 -- LOCAL -SYSTEM
TEMP -- LOCAL -UNIFORM
USERS -- LOCAL -SYSTEM
Dictionary Managed Tablespaces (DMT):
Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in "dictionary managed" mode. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes agianst these tables. As only one process can acquire the ST enque at a given time, this often lead to contention.
Execute the following statement to create a dictionary managed tablespace:
SQL> CREATE TABLESPACE tblspc1 DATAFILE '/oradata/tblspc1_01.dbf' SIZE 50M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);
Locally Managed Tablespaces (LMT):
Using LMT, each tablespace manages it's own free and used space within a bitmap structure stored in one of the tablespace's data files. Each bit corresponds to a database block or group of blocks. Execute one of the following statements to create a locally managed tablespace:
SQL> CREATE TABLESPACE tblspc2 DATAFILE '/oradata/tblspc2_01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
SQL> CREATE TABLESPACE tblspc3 DATAFILE '/oradata/tblspc3_01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
Note the difference between AUTOALLOCATE and UNIFORM SIZE:
AUTOALLOCATE specifies that extent sizes are system managed. Oracle will choose "optimal" next extent sizes starting with 64KB. As the segment grows larger extent sizes will increase to 1MB, 8MB, and eventually to 64MB. This is the recommended option for a low or unmanaged environment.
UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes (use K or M to specify the extent size in kilobytes or megabytes). The default size is 1M. The uniform extent size of a locally managed tablespace cannot be overridden when a schema object, such as a table or an index, is created.
Also not, if you specify, LOCAL, you cannot specify DEFAULT STORAGE, MINIMUM EXTENT or TEMPORARY.
Advantages of Locally Managed Tablespaces:
Eliminates the need for recursive SQL operations against the data dictionary (UET$ and FET$ tables)
Reduce contention on data dictionary tables (single ST enqueue)
Locally managed tablespaces eliminate the need to periodically coalesce free space (automatically tracks adjacent free space)
Changes to the extent bitmaps do not generate rollback information
Locally Managed SYSTEM Tablespace:
From Oracle9i release 9.2 one can change the SYSTEM tablespace to locally managed. Further, if you create a database with DBCA (Database Configuration Assistant), it will have a locally managed SYSTEM tablespace by default. The following restrictions apply:
No dictionary-managed tablespace in the database can be READ WRITE.
You cannot create new dictionary managed tablespaces
You cannot convert any dictionary managed tablespaces to local
Thus, it is best only to convert the SYSTEM tablespace to LMT after all other tablespaces are migrated to LMT.
Segment Space Management in LMT:
From Oracle 9i, one can not only have bitmap managed tablespaces, but also bitmap managed segments when setting Segment Space Management to AUTO for a tablespace. Look at this example:
SQL> CREATE TABLESPACE tblspc4 DATAFILE '/oradata/tblspc4_01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
Segment Space Management eliminates the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects. The Automatic Segment Space Management feature improves the performance of concurrent DML operations significantly since different parts of the bitmap can be used simultaneously eliminating serialization for free space lookups against the FREELSITS. This is of particular importance when using RAC, or if "buffer busy waits" are deteted.
Convert between LMT and DMT:
The DBMS_SPACE_ADMIN package allows DBAs to quickly and easily convert between LMT and DMT mode. Look at these examples:
SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('tblspc1');
PL/SQL procedure successfully completed.
SQL>exec dbms_space_admin.Tablespace_Migrate_FROM_Local('tblspc2');
PL/SQL procedure successfully completed.
Installing the April 2007 Critical Patch Update on Windows
This article describes the procedure to install the April 2007 CPU patch on Oracle Database Release 10.1.0.5. The Patch Number is p5907304_10105_WINNT.zip
SYSTEM Overview
Platforms : Windows XP SP2
Database : 10.1.0.5.0
OPatch Utility Information
You must use the OPatch utility release 1.0.0.0.57 or later. You can download it from OracleMetaLink with patch 2617419.
Check Oracle Inventory Setup
D:\>set ORACLE_SID=TEST
D:\>set ORACLE_HOME=D:\oracle\product\10.1.0\db_1
D:\>cd D:\oracle\product\10.1.0\db_1\opatch
D:\oracle\product\10.1.0\Db_1\OPatch>opatch lsinventory
Oracle Interim Patch Installer version 1.0.0.0.53
Note: if Opatch utility version is <= 1.0.0.0.53 then you have to download OPATCH utility from the Metalink site.
[output not attached]
Oracle Home = D:\oracle\product\10.1.0\db_1
Location of Oracle Universal Installer components = D:\oracle\product\10.1.0\db_1\oui
Location of OraInstaller.jar = "D:\oracle\product\10.1.0\db_1\oui\jlib"
Oracle Universal Installer shared library = D:\oracle\product\10.1.0\db_1\oui\lib\win32\oraInstaller.dll
Location of Oracle Inventory Pointer = N/A
Location of Oracle Inventory = D:\oracle\product\10.1.0\db_1\inventory
Path to Java = "D:\oracle\product\10.1.0\db_1\jre\1.4.2\bin\java.exe"
Log file = D:\oracle\product\10.1.0\db_1/.patch_storage//*.log
Creating log file "D:\oracle\product\10.1.0\db_1\.patch_storage\LsInventory__12-13-2007_10-18-45.log"
Result:
There is no Interim Patch
OPatch succeeded.
OPatch returns with error code = 0
Preinstallation Instructions
1. Ensure that your Oracle Database installation is the same release for which you are applying this patch.
2. Back up the current database that is installed
-->ORACLE_HOME or ORA INVENTORY
3. Perform a clean shutdown of all Oracle services
D:\>net stop OracleServiceTEST
The following services are dependent on the OracleServiceTEST service.
Stopping the OracleServiceTEST service will also stop these services.
OracleDBConsoleTEST
Do you want to continue this operation? (Y/N) [N]: Y
The OracleDBConsoleTEST service is stopping...............
The OracleDBConsoleTEST service was stopped successfully.
The OracleServiceTEST service is stopping...........
The OracleServiceTEST service was stopped successfully.
D:\>net stop OracleServiceRCAT
The OracleServiceRCAT service is stopping......
The OracleServiceRCAT service was stopped successfully.
D:\>LSNRCTL stop
LSNRCTL for 32-bit Windows: Version 10.1.0.5.0 - Production on 13-DEC-2007 10:30:50
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully
D:\>net stop OracleCSService
The OracleCSService service was stopped successfully.
D:\>net stop OracleOraDb10g_home1iSQL*Plus
..
The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.
4. Set the PERL5LIB and PATH environment variables to point to Perl in Oracle Home
D:\>set PERL5LIB=D:\oracle\product\10.1.0\db_1\perl\5.6.1\lib;%PERL5LIB%
D:\>set PATH=D:\oracle\product\10.1.0\db_1\perl\5.6.1\bin\MSWin32-x86;%PATH%
5. set ORACLE_HOME variable
D:\>set ORACLE_HOME=D:\oracle\product\10.1.0\db_1
6. Check if the java and jar executables are present in your Oracle Home
-->JAVA = %ORACLE_HOME%\jre\\bin
-->JAR = %ORACLE_HOME%\jdk\bin
7. Go to directory where you downloaded the patch
D:\oracle\product\10.1.0\Db_1\OPatch>opatch apply D:\5907304
Post Installation Instructions
1. Start All Oracle Services.
D:\>net start OracleServiceTEST
D:\>net start OracleServiceRCAT
D:\>set ORACLE_SID=TEST
D:\>emctl start dbconsole
D:\>LSNRCTL start
D:\>net start OracleCSService
D:\>net start OracleOraDb10g_home1iSQL*Plus
2. For each database instance running out of the ORACLE_HOME being patched, connect to the database using SQL*Plus as SYSDBA and run catcpu.sql as follows:
D:\>rem go to CPU directory
D:\>CD D:\oracle\product\10.1.0\db_1\CPU\CPUApr2007
D:\oracle\product\10.1.0\Db_1\cpu\cpuapr2007>sqlplus /nolog
SQL*Plus: Release 10.1.0.5.0 - Production on Fri Dec 14 14:31:43 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
TEST
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MIGRATE
ORACLE instance started.
Total System Global Area 1023410176 bytes
Fixed Size 782304 bytes
Variable Size 74190880 bytes
Database Buffers 947912704 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> @CATCPU.SQL
[output not attached]
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
If catcpu.sql reports any Invalid Objects, compile the invalid objects as follows
D:\>cd D:\oracle\product\10.1.0\db_1\rdbms\admin
D:\oracle\product\10.1.0\Db_1\RDBMS\ADMIN>sqlplus /nolog
SQL*Plus: Release 10.1.0.5.0 - Production on Fri Dec 14 14:55:21 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 1023410176 bytes
Fixed Size 782304 bytes
Variable Size 74190880 bytes
Database Buffers 947912704 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> @utlprp.sql
SQL> select OBJECT_NAME from dba_objects where status = 'INVALID';
no rows selected
3. If any databases have been created without using DBCA (that is, using the command line), and you need to monitor such a database by using Oracle Enterprise Manager 10g, follow these steps:
$ sqlplus SYS / AS SYSDBA
SQL> ALTER USER DBSNMP ACCOUNT UNLOCK
SQL> PASSWORD DBSNMP
Changing password for DBSNMP
New password: new_password
Retype new password: new_password
SYSTEM Overview
Platforms : Windows XP SP2
Database : 10.1.0.5.0
OPatch Utility Information
You must use the OPatch utility release 1.0.0.0.57 or later. You can download it from OracleMetaLink with patch 2617419.
Check Oracle Inventory Setup
D:\>set ORACLE_SID=TEST
D:\>set ORACLE_HOME=D:\oracle\product\10.1.0\db_1
D:\>cd D:\oracle\product\10.1.0\db_1\opatch
D:\oracle\product\10.1.0\Db_1\OPatch>opatch lsinventory
Oracle Interim Patch Installer version 1.0.0.0.53
Note: if Opatch utility version is <= 1.0.0.0.53 then you have to download OPATCH utility from the Metalink site.
[output not attached]
Oracle Home = D:\oracle\product\10.1.0\db_1
Location of Oracle Universal Installer components = D:\oracle\product\10.1.0\db_1\oui
Location of OraInstaller.jar = "D:\oracle\product\10.1.0\db_1\oui\jlib"
Oracle Universal Installer shared library = D:\oracle\product\10.1.0\db_1\oui\lib\win32\oraInstaller.dll
Location of Oracle Inventory Pointer = N/A
Location of Oracle Inventory = D:\oracle\product\10.1.0\db_1\inventory
Path to Java = "D:\oracle\product\10.1.0\db_1\jre\1.4.2\bin\java.exe"
Log file = D:\oracle\product\10.1.0\db_1/.patch_storage//*.log
Creating log file "D:\oracle\product\10.1.0\db_1\.patch_storage\LsInventory__12-13-2007_10-18-45.log"
Result:
There is no Interim Patch
OPatch succeeded.
OPatch returns with error code = 0
Preinstallation Instructions
1. Ensure that your Oracle Database installation is the same release for which you are applying this patch.
2. Back up the current database that is installed
-->ORACLE_HOME or ORA INVENTORY
3. Perform a clean shutdown of all Oracle services
D:\>net stop OracleServiceTEST
The following services are dependent on the OracleServiceTEST service.
Stopping the OracleServiceTEST service will also stop these services.
OracleDBConsoleTEST
Do you want to continue this operation? (Y/N) [N]: Y
The OracleDBConsoleTEST service is stopping...............
The OracleDBConsoleTEST service was stopped successfully.
The OracleServiceTEST service is stopping...........
The OracleServiceTEST service was stopped successfully.
D:\>net stop OracleServiceRCAT
The OracleServiceRCAT service is stopping......
The OracleServiceRCAT service was stopped successfully.
D:\>LSNRCTL stop
LSNRCTL for 32-bit Windows: Version 10.1.0.5.0 - Production on 13-DEC-2007 10:30:50
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully
D:\>net stop OracleCSService
The OracleCSService service was stopped successfully.
D:\>net stop OracleOraDb10g_home1iSQL*Plus
..
The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.
4. Set the PERL5LIB and PATH environment variables to point to Perl in Oracle Home
D:\>set PERL5LIB=D:\oracle\product\10.1.0\db_1\perl\5.6.1\lib;%PERL5LIB%
D:\>set PATH=D:\oracle\product\10.1.0\db_1\perl\5.6.1\bin\MSWin32-x86;%PATH%
5. set ORACLE_HOME variable
D:\>set ORACLE_HOME=D:\oracle\product\10.1.0\db_1
6. Check if the java and jar executables are present in your Oracle Home
-->JAVA = %ORACLE_HOME%\jre\\bin
-->JAR = %ORACLE_HOME%\jdk\bin
7. Go to directory where you downloaded the patch
D:\oracle\product\10.1.0\Db_1\OPatch>opatch apply D:\5907304
Post Installation Instructions
1. Start All Oracle Services.
D:\>net start OracleServiceTEST
D:\>net start OracleServiceRCAT
D:\>set ORACLE_SID=TEST
D:\>emctl start dbconsole
D:\>LSNRCTL start
D:\>net start OracleCSService
D:\>net start OracleOraDb10g_home1iSQL*Plus
2. For each database instance running out of the ORACLE_HOME being patched, connect to the database using SQL*Plus as SYSDBA and run catcpu.sql as follows:
D:\>rem go to CPU directory
D:\>CD D:\oracle\product\10.1.0\db_1\CPU\CPUApr2007
D:\oracle\product\10.1.0\Db_1\cpu\cpuapr2007>sqlplus /nolog
SQL*Plus: Release 10.1.0.5.0 - Production on Fri Dec 14 14:31:43 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
TEST
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MIGRATE
ORACLE instance started.
Total System Global Area 1023410176 bytes
Fixed Size 782304 bytes
Variable Size 74190880 bytes
Database Buffers 947912704 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> @CATCPU.SQL
[output not attached]
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
If catcpu.sql reports any Invalid Objects, compile the invalid objects as follows
D:\>cd D:\oracle\product\10.1.0\db_1\rdbms\admin
D:\oracle\product\10.1.0\Db_1\RDBMS\ADMIN>sqlplus /nolog
SQL*Plus: Release 10.1.0.5.0 - Production on Fri Dec 14 14:55:21 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 1023410176 bytes
Fixed Size 782304 bytes
Variable Size 74190880 bytes
Database Buffers 947912704 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> @utlprp.sql
SQL> select OBJECT_NAME from dba_objects where status = 'INVALID';
no rows selected
3. If any databases have been created without using DBCA (that is, using the command line), and you need to monitor such a database by using Oracle Enterprise Manager 10g, follow these steps:
$ sqlplus SYS / AS SYSDBA
SQL> ALTER USER DBSNMP ACCOUNT UNLOCK
SQL> PASSWORD DBSNMP
Changing password for DBSNMP
New password: new_password
Retype new password: new_password
Upgrade the Oracle database from 10.1.0.3.0 to 10.1.0.5.0
This article describes the process of upgrading the Oracle Database Server 10g Release 10.1.0.3 to Oracle Database 10g Release 10.1.0.5 on Windows XP SP2.
1. Patch Set Overview
You can apply the Oracle Database 10g release 10.1.0.5 patch set to the following Oracle Database 10g release 1 (10.1.0.2, 10.1.0.3, or 10.1.0.4) installations.
2. Oracle Universal Installer Version Requirements
This patch set includes Oracle Universal Installer release 10.1.0.5, which is also installed when you install this patch set.
3. System Requirements
Hardware requirement : Intel Pentium
OS requirement : Windows XP Prof SP1 or later
Oracle DB : 10gr1(10.1.0.2) or later
4. Pre-installation Tasks
1. Identify the Oracle Database Installation
This is not a complete software distribution. You must install it in an existing Oracle Database 10g Oracle home.
2. Download and extract the p4505133_10105_WINNT.zip patch set.
3. Shut Down Oracle Databases
D:\>sqlplus sys@TEST/pwd as sysdba
SQL>shutdown immediate;
D:\>sqlplus sys@RCAT/pwd as sysdba
SQL>shutdown immediate;
4. Stop All Services
D:\>net stop OracleOraDb10g_home1iSQL*Plus
..
The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.
D:\>net stop OracleServiceTEST
The following services are dependent on the OracleServiceTEST service.
Stopping the OracleServiceTEST service will also stop these services.
OracleDBConsoleTEST
Do you want to continue this operation? (Y/N) [N]: Y
The OracleDBConsoleTEST service is stopping....
The OracleDBConsoleTEST service was stopped successfully.
The OracleServiceTEST service is stopping.
The OracleServiceTEST service was stopped successfully.
D:\>net stop OracleServiceRCAT
The OracleServiceRCAT service is stopping.......
The OracleServiceRCAT service was stopped successfully.
5. Stop the listener:
D:\>LSNRCTL stop
LSNRCTL for 32-bit Windows: Version 10.1.0.3.0 - Production on 28-DEC-2007 11:05:13
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully
D:\>net stop OracleCSService
The OracleCSService service was stopped successfully.
6. Back up the SYSTEM.
Installation Tasks
1. Run SETUP.EXE from where you downloaded/extracted your patch.
In the Name field in the Destination section, select the 10.1.0.x Oracle home that you want to update from the drop down list, then click Next.
On the Summary screen, click Install.
Upgrading Release 10.1 to Oracle Database 10g Release 1 (10.1.0.5)
1.Check SYSTEM TBS should have 10mb Free Space.
2.SHARED_POOL_SIZE or JAVA_POOL_SIZE at least 150 MB.
SQL> alter tablespace SYSTEM
2 add datafile 'D:\oracle\product\10.1.0\oradata\TEST\system02.dbf' size 20m;
Tablespace altered.
SQL> alter system set SHARED_POOL_SIZE = 150M SCOPE=spfile;
System altered.
SQL> alter system set JAVA_POOL_SIZE = 150M SCOPE=spfile;
System altered.
SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP UPGRADE
ORACLE instance started.
Total System Global Area 1023410176 bytes
Fixed Size 782304 bytes
Variable Size 74190880 bytes
Database Buffers 947912704 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> SPOOL patch.log
SQL> @D:\oracle\product\10.1.0\db_1\rdbms\admin\catpatch.sql
DOC>######################################################################
DOC>######################################################################
DOC> The following PL/SQL block will cause an ORA-20000 error and
DOC> terminate the current SQLPLUS session if the user is not SYS.
DOC> Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
PL/SQL procedure successfully completed.
....
[output not attached]
SQL> SPOOL OFF
Restart the database
SQL>SHUTDOWN
SQL>STARTUP
Run the utlrp.sql script to recompile all invalid PL/SQL packages
SQL> @D:\oracle\product\10.1.0\db_1\rdbms\admin\utlrp.sql
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod
PL/SQL Release 10.1.0.5.0 - Production
CORE 10.1.0.5.0 Production
TNS for 32-bit Windows: Version 10.1.0.5.0 - Production
NLSRTL Version 10.1.0.5.0 - Production
Known Issues
select dbms_java.full_ncomp_enabled from dual
*
ERROR at line 1:
ORA-29558: JAccelerator (NCOMP) not installed. Refer to Install Guide for
instructions.
ORA-06512: at "SYS.DBMS_JAVA", line 236
IGNORE
---> If you do not use Java in the database, then ignore this error message.
Don't Ignore
--->If you use Java in the database, then install the NCOMP libraries by selecting the Oracle Database 10g Products installation type from the Oracle Database Companion CD.
If RMAN CATALOG is also configure then upgrade RMAN CATALOG
D:\>rman CATALOG=rman/cat@RCAT
Recovery Manager: Release 10.1.0.5.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> UPGRADE CATALOG;
recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade
RMAN> UPGRADE CATALOG;
recovery catalog upgraded to version 10.01.00
DBMS_RCVMAN package upgraded to version 10.01.00
DBMS_RCVCAT package upgraded to version 10.01.00
1. Patch Set Overview
You can apply the Oracle Database 10g release 10.1.0.5 patch set to the following Oracle Database 10g release 1 (10.1.0.2, 10.1.0.3, or 10.1.0.4) installations.
2. Oracle Universal Installer Version Requirements
This patch set includes Oracle Universal Installer release 10.1.0.5, which is also installed when you install this patch set.
3. System Requirements
Hardware requirement : Intel Pentium
OS requirement : Windows XP Prof SP1 or later
Oracle DB : 10gr1(10.1.0.2) or later
4. Pre-installation Tasks
1. Identify the Oracle Database Installation
This is not a complete software distribution. You must install it in an existing Oracle Database 10g Oracle home.
2. Download and extract the p4505133_10105_WINNT.zip patch set.
3. Shut Down Oracle Databases
D:\>sqlplus sys@TEST/pwd as sysdba
SQL>shutdown immediate;
D:\>sqlplus sys@RCAT/pwd as sysdba
SQL>shutdown immediate;
4. Stop All Services
D:\>net stop OracleOraDb10g_home1iSQL*Plus
..
The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.
D:\>net stop OracleServiceTEST
The following services are dependent on the OracleServiceTEST service.
Stopping the OracleServiceTEST service will also stop these services.
OracleDBConsoleTEST
Do you want to continue this operation? (Y/N) [N]: Y
The OracleDBConsoleTEST service is stopping....
The OracleDBConsoleTEST service was stopped successfully.
The OracleServiceTEST service is stopping.
The OracleServiceTEST service was stopped successfully.
D:\>net stop OracleServiceRCAT
The OracleServiceRCAT service is stopping.......
The OracleServiceRCAT service was stopped successfully.
5. Stop the listener:
D:\>LSNRCTL stop
LSNRCTL for 32-bit Windows: Version 10.1.0.3.0 - Production on 28-DEC-2007 11:05:13
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully
D:\>net stop OracleCSService
The OracleCSService service was stopped successfully.
6. Back up the SYSTEM.
Installation Tasks
1. Run SETUP.EXE from where you downloaded/extracted your patch.
In the Name field in the Destination section, select the 10.1.0.x Oracle home that you want to update from the drop down list, then click Next.
On the Summary screen, click Install.
Upgrading Release 10.1 to Oracle Database 10g Release 1 (10.1.0.5)
1.Check SYSTEM TBS should have 10mb Free Space.
2.SHARED_POOL_SIZE or JAVA_POOL_SIZE at least 150 MB.
SQL> alter tablespace SYSTEM
2 add datafile 'D:\oracle\product\10.1.0\oradata\TEST\system02.dbf' size 20m;
Tablespace altered.
SQL> alter system set SHARED_POOL_SIZE = 150M SCOPE=spfile;
System altered.
SQL> alter system set JAVA_POOL_SIZE = 150M SCOPE=spfile;
System altered.
SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP UPGRADE
ORACLE instance started.
Total System Global Area 1023410176 bytes
Fixed Size 782304 bytes
Variable Size 74190880 bytes
Database Buffers 947912704 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> SPOOL patch.log
SQL> @D:\oracle\product\10.1.0\db_1\rdbms\admin\catpatch.sql
DOC>######################################################################
DOC>######################################################################
DOC> The following PL/SQL block will cause an ORA-20000 error and
DOC> terminate the current SQLPLUS session if the user is not SYS.
DOC> Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
PL/SQL procedure successfully completed.
....
[output not attached]
SQL> SPOOL OFF
Restart the database
SQL>SHUTDOWN
SQL>STARTUP
Run the utlrp.sql script to recompile all invalid PL/SQL packages
SQL> @D:\oracle\product\10.1.0\db_1\rdbms\admin\utlrp.sql
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod
PL/SQL Release 10.1.0.5.0 - Production
CORE 10.1.0.5.0 Production
TNS for 32-bit Windows: Version 10.1.0.5.0 - Production
NLSRTL Version 10.1.0.5.0 - Production
Known Issues
select dbms_java.full_ncomp_enabled from dual
*
ERROR at line 1:
ORA-29558: JAccelerator (NCOMP) not installed. Refer to Install Guide for
instructions.
ORA-06512: at "SYS.DBMS_JAVA", line 236
IGNORE
---> If you do not use Java in the database, then ignore this error message.
Don't Ignore
--->If you use Java in the database, then install the NCOMP libraries by selecting the Oracle Database 10g Products installation type from the Oracle Database Companion CD.
If RMAN CATALOG is also configure then upgrade RMAN CATALOG
D:\>rman CATALOG=rman/cat@RCAT
Recovery Manager: Release 10.1.0.5.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> UPGRADE CATALOG;
recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade
RMAN> UPGRADE CATALOG;
recovery catalog upgraded to version 10.01.00
DBMS_RCVMAN package upgraded to version 10.01.00
DBMS_RCVCAT package upgraded to version 10.01.00
Finding your OS and Database 32 bits or 64 bits
Want to know whether your Database / Operating System is running a 32-bit or 64-bit software?
DBAs often need to know if they should install Oracle 32-bit or 64-bit software on a given server. If the operating system support 64-bit applications, the 64-bit Oracle distribution can be loaded. Otherwise, the 32-bit distribution must be installed. The following Unix commands will tell you whether your OS is running 32-bits or 64-bits:
Solaris - isainfo -v (this command doesn't exist on Solaris 2.6 because it is only 32-bits)
HP-UX - getconf KERNEL_BITS
AIX - bootinfo -K
LINUX - uname -a
If your Linux OS is 32 bit the output will be like below:
Linux oraclesrvr 2.6.18-8.el5PAE #1 SMP Tue Jun 5 23:39:57 EDT 2007 i686 i686 i386 GNU/Linux
If your Linux OS is 64 bit the output will be like below:
Linux aptserver 2.6.18-8.el5xen #1 SMP Tue Jun 5 23:53:34 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux
If you need to know Oracle 32-bit or 64-bit software is currently installed on a system, connect using a command line utility like sqlplus and look at the banner. If you are running 64-bit software, it will be mentioned in the banner. If nothing is listed, you are running on a 32-bit base.
If your Oracle Software is 64 bit the output will be like below:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bi
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Linux: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production
If your Oracle Software is 32 bit the output will be like below:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Linux: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production
DBAs often need to know if they should install Oracle 32-bit or 64-bit software on a given server. If the operating system support 64-bit applications, the 64-bit Oracle distribution can be loaded. Otherwise, the 32-bit distribution must be installed. The following Unix commands will tell you whether your OS is running 32-bits or 64-bits:
Solaris - isainfo -v (this command doesn't exist on Solaris 2.6 because it is only 32-bits)
HP-UX - getconf KERNEL_BITS
AIX - bootinfo -K
LINUX - uname -a
If your Linux OS is 32 bit the output will be like below:
Linux oraclesrvr 2.6.18-8.el5PAE #1 SMP Tue Jun 5 23:39:57 EDT 2007 i686 i686 i386 GNU/Linux
If your Linux OS is 64 bit the output will be like below:
Linux aptserver 2.6.18-8.el5xen #1 SMP Tue Jun 5 23:53:34 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux
If you need to know Oracle 32-bit or 64-bit software is currently installed on a system, connect using a command line utility like sqlplus and look at the banner. If you are running 64-bit software, it will be mentioned in the banner. If nothing is listed, you are running on a 32-bit base.
If your Oracle Software is 64 bit the output will be like below:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bi
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Linux: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production
If your Oracle Software is 32 bit the output will be like below:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Linux: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production
Thursday, December 27, 2007
Relocating Database Files
The CRD&T files are called as Database Files:
1. Control files
2. Redo log files
3. Datafiles & Tempfiles
At some moments we need to move or relocate the Database files from original(old) location to new location.
STEPS FOR RELOCATING THESE DATABASE FILES:
I.How to move DATAFILES from old to new location(not SYSTEM,UNDO & TEMP tablespaces'):
Step 1: Take tablespace offline
SQL> alter tablespace tbsname OFFLINE;
Step 2:Use OS level command to MOVE datafiles from old to new location.
$ mv 'old location' to 'new location'
Step 3: Rename datafiles in database.
SQL> alter database rename file 'old location' to 'new location';
Step 4: Take tablespace ONLINE
SQL> alter tablespace tbsname ONLINE;
II.For SYSTEM or UNDO tablespace:
Step 1: Shutdown database
SQL> shutdown immediate;
Step 2: Move datafiles from old to new location using OS command.
$ mv 'old location' 'new location'
Step 3: Startup database in mount stage.
SQL> startup mount;
Step 4: Rename the datafile
SQL> alter database rename file 'old location' to 'new location';
Step 5: Open the database for normal use
SQL> alter database open;
III.How to move control file from old to new location:
Step 1: Shutdown the Database
SQL> shutdown immediate;
Step 2: Move controlfile from old to new location
$ mv old location new location
Step 3: Edit CONTROL_FILES parameter in pfile and change location from old to new
CONTROL_FILES='new location'
Step 4: recreate SPFILE from PFILE
SQL> create spfile from pfile;
Step 5: startup database
SQL> startup;
IV.How to move REDO LOG MEMBER from old to new location
Step 1: shutdown database
SQL> shutdown immediate;
Step 2: Move redo log member from old to new location
$ mv oldlocation newlocation
Step 3: startup database with mount stage
SQL> startup mount;
Step 4: rename redo log member in database
SQL> alter database rename file 'old location' to 'new location';
Step 5: open database for normal use
SQL> alter database open;
V. We cannot relocate the tempfile, if we need to relocate it(tempfile) then the best option is to recreate the temp tablespace in the new location.
1. Control files
2. Redo log files
3. Datafiles & Tempfiles
At some moments we need to move or relocate the Database files from original(old) location to new location.
STEPS FOR RELOCATING THESE DATABASE FILES:
I.How to move DATAFILES from old to new location(not SYSTEM,UNDO & TEMP tablespaces'):
Step 1: Take tablespace offline
SQL> alter tablespace tbsname OFFLINE;
Step 2:Use OS level command to MOVE datafiles from old to new location.
$ mv 'old location' to 'new location'
Step 3: Rename datafiles in database.
SQL> alter database rename file 'old location' to 'new location';
Step 4: Take tablespace ONLINE
SQL> alter tablespace tbsname ONLINE;
II.For SYSTEM or UNDO tablespace:
Step 1: Shutdown database
SQL> shutdown immediate;
Step 2: Move datafiles from old to new location using OS command.
$ mv 'old location' 'new location'
Step 3: Startup database in mount stage.
SQL> startup mount;
Step 4: Rename the datafile
SQL> alter database rename file 'old location' to 'new location';
Step 5: Open the database for normal use
SQL> alter database open;
III.How to move control file from old to new location:
Step 1: Shutdown the Database
SQL> shutdown immediate;
Step 2: Move controlfile from old to new location
$ mv old location new location
Step 3: Edit CONTROL_FILES parameter in pfile and change location from old to new
CONTROL_FILES='new location'
Step 4: recreate SPFILE from PFILE
SQL> create spfile from pfile;
Step 5: startup database
SQL> startup;
IV.How to move REDO LOG MEMBER from old to new location
Step 1: shutdown database
SQL> shutdown immediate;
Step 2: Move redo log member from old to new location
$ mv oldlocation newlocation
Step 3: startup database with mount stage
SQL> startup mount;
Step 4: rename redo log member in database
SQL> alter database rename file 'old location' to 'new location';
Step 5: open database for normal use
SQL> alter database open;
V. We cannot relocate the tempfile, if we need to relocate it(tempfile) then the best option is to recreate the temp tablespace in the new location.
Wednesday, December 12, 2007
Simple FTP setup in Linux
By default FTP comes disabled on stock RedHat/Oracle Enterprise/Centos server installs. In some occasions I find the need to enable FTP.
Below I will show you how to do it:
Become the root user on your server.
Change to the /etc/xinetd.d directory
cd /etc/xinetd.d/
Edit the gssftp file
vi gssftp
Change the line that says disable = yes to disable = no. Also remove the –a option in the server_args line.
Your config file should now look like the one below. Save the file and exit vi.
service ftp
{
flags = REUSE
socket_type = stream
wait = no
user = root
server = /usr/kerberos/sbin/ftpd
server_args = -l
log_on_failure += USERID
disable = no
}
The -a option removes the following restriction, as described in the man ftpd page.
-a
Connections are only allowed for users who can authenticate (via the ftp AUTH mechanism) and who are authorized to connect to the named account without a password. (Anonymous ftp may also be allowed if it is configured.)
Restart the xinetd daemon.
/etc/init.d/xinetd restart
You should now be able to FTP to your server.
That’s it, you’re FTP service is now enabled. Now let me explain when I actually use
FTP. Sometimes I will transfer very large files or amounts of data between my servers (many gigabytes worth). I will enable FTP temporarily to do this since FTP can do these transfers a little more quickly than SFTP because it doesn’t have as
much overhead. After I’m done with the transfer, I then disable FTP.
EXAMPLES
ftp rmsi.oracle.com
This command will attempt to connect to the ftp server at rmsi.oracle.com. If it succeeds, it will ask you to log in using a username and password. Public ftp servers often allow you to log in using the username "anonymous" and your email address as password. Once you are logged in you can get a list of the available ftp commands using the help function:
ftp> help
This lists the commands that you can use to show the directory contents, transfer files, and delete files.
ftp> ls
This command prints the names of the files and subdirectories in the current directory on the remote computer.
ftp> cd customers
This command changes the current directory to the subdirecotry "customers", if it exists.
ftp> cd ..
Changes the current directory to the parent direcotry.
ftp> lcd images
Changes the current directory on the local computer to "images", if it exists.
ftp> ascii
Changes to "ascii" mode for transferring text files.
ftp> binary
Changes to "binary" mode for transferring all files that are not text files.
ftp> get image1.jpg
Downloads the file image1.jpg from the remote computer to the local computer. Warning: If there already is file with the same name it will be overwritten.
ftp> put image2.jpg
Uploads the file image2.jpg from the local computer to the remote computer. Warning: If there already is file with the same name it will be overwritten.
ftp> !ls
A '!' in front will execute the specified command on the local computer. So '!ls' lists the file names and directory names of the current directory on the local computer.
ftp> mget *.jpg
With mget you can download multiple images. This command downloads all files that end with ".jgp".
ftp> mput *.jpg
Uploads all files that end with ".jgp".
ftp> mdelete *.jpg
Deletes all files that end with ".jgp".
ftp> prompt
Turns iteractive mode on or off so that commands on multiple files are executed without user confirmation.
ftp> quit
Exits the ftp program.
Below I will show you how to do it:
Become the root user on your server.
Change to the /etc/xinetd.d directory
cd /etc/xinetd.d/
Edit the gssftp file
vi gssftp
Change the line that says disable = yes to disable = no. Also remove the –a option in the server_args line.
Your config file should now look like the one below. Save the file and exit vi.
service ftp
{
flags = REUSE
socket_type = stream
wait = no
user = root
server = /usr/kerberos/sbin/ftpd
server_args = -l
log_on_failure += USERID
disable = no
}
The -a option removes the following restriction, as described in the man ftpd page.
-a
Connections are only allowed for users who can authenticate (via the ftp AUTH mechanism) and who are authorized to connect to the named account without a password. (Anonymous ftp may also be allowed if it is configured.)
Restart the xinetd daemon.
/etc/init.d/xinetd restart
You should now be able to FTP to your server.
That’s it, you’re FTP service is now enabled. Now let me explain when I actually use
FTP. Sometimes I will transfer very large files or amounts of data between my servers (many gigabytes worth). I will enable FTP temporarily to do this since FTP can do these transfers a little more quickly than SFTP because it doesn’t have as
much overhead. After I’m done with the transfer, I then disable FTP.
EXAMPLES
ftp rmsi.oracle.com
This command will attempt to connect to the ftp server at rmsi.oracle.com. If it succeeds, it will ask you to log in using a username and password. Public ftp servers often allow you to log in using the username "anonymous" and your email address as password. Once you are logged in you can get a list of the available ftp commands using the help function:
ftp> help
This lists the commands that you can use to show the directory contents, transfer files, and delete files.
ftp> ls
This command prints the names of the files and subdirectories in the current directory on the remote computer.
ftp> cd customers
This command changes the current directory to the subdirecotry "customers", if it exists.
ftp> cd ..
Changes the current directory to the parent direcotry.
ftp> lcd images
Changes the current directory on the local computer to "images", if it exists.
ftp> ascii
Changes to "ascii" mode for transferring text files.
ftp> binary
Changes to "binary" mode for transferring all files that are not text files.
ftp> get image1.jpg
Downloads the file image1.jpg from the remote computer to the local computer. Warning: If there already is file with the same name it will be overwritten.
ftp> put image2.jpg
Uploads the file image2.jpg from the local computer to the remote computer. Warning: If there already is file with the same name it will be overwritten.
ftp> !ls
A '!' in front will execute the specified command on the local computer. So '!ls' lists the file names and directory names of the current directory on the local computer.
ftp> mget *.jpg
With mget you can download multiple images. This command downloads all files that end with ".jgp".
ftp> mput *.jpg
Uploads all files that end with ".jgp".
ftp> mdelete *.jpg
Deletes all files that end with ".jgp".
ftp> prompt
Turns iteractive mode on or off so that commands on multiple files are executed without user confirmation.
ftp> quit
Exits the ftp program.
Tuesday, November 06, 2007
Changing the current Database Name and DB ID
CHANGING DBNAME & DBID
-----------------------
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT
$nid TARGET=sys/sys@swdm DBNAME=swm
Change database ID and database name SWDM to SWM? (Y/[N]) => Y
SQL>SHUTDOWN IMMEDIATE
Modify the DB_NAME parameter in the initialization parameter file. The startup will result in an error but proceed anyway.
SQL>STARTUP MOUNT
SQL>ALTER SYSTEM SET DB_NAME=SWM SCOPE=SPFILE;
SQL>SHUTDOWN IMMEDIATE
Create a new password file:
$cd /u01/app/oracle/product/10.1.0/db_1/dbs/
$orapwd file=/u01/app/oracle/product/10.1.0/db_1/dbs/orapw password=sys entries=10
If you are using UNIX/Linux simply reset the ORACLE_SID environment variable:
$ORACLE_SID=SWM; export ORACLE_SID
Rename the SPFILE to match the new DBNAME.
If you are using Windows you must recreate the service so the correct name and parameter file are used:
oradim -delete -sid SWDM
oradim -new -sid SWM -intpwd password -startmode a -pfile c:\oracle\920\database\spfileSWM.ora
Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:
$lsnrctl reload
Open the database with RESETLOGS:
SQL>STARTUP MOUNT
SQL>ALTER DATABASE OPEN RESETLOGS;
Backup the database.
==========================================================================
CHANGING DBNAME Only
---------------------
Repeat the process as before except use the following command to start the DBNEWID utility:
$nid TARGET=sys/sys@SWDM DBNAME=swm SETNAME=YES
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>ALTER SYSTEM SET DB_NAME=SWM SCOPE=spfile;
SQL>SHUDOWN IMMEDIATE;
On Windows:
C:\> orapwd file= C:\oracle\product\10.1.0\db_1\database\pwdSWM.ora password=xxxxxxx entries=10
On Unix:
$ orapwd file=/u01/app/oracle/product/10.1.0/db_1/dbs/orapw password=sys entries=10
SQL>STARTUP;
SQL>SHOW PARAMETER DB_NAME
We have changed only the database name, The SETNAME parameter tells the DBNEWID utility to only alter the database name. and
not the database ID, it is not necessary to use the RESETLOGS option when you open the database
Backup the database.
===========================================================================
CHANGING DBID Only
-------------------
Backup the database.
Mount the database after a clean shutdown:
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT
Invoke the DBNEWID utility (nid) from the command line using a user with SYSDBA privilege. Do not specify a new DBNAME:
$nid TARGET=sys/password@SWDM
Shutdown and open the database with RESETLOGS:
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT
SQL>ALTER DATABASE OPEN RESETLOGS;
Backup the database.
-----------------------
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT
$nid TARGET=sys/sys@swdm DBNAME=swm
Change database ID and database name SWDM to SWM? (Y/[N]) => Y
SQL>SHUTDOWN IMMEDIATE
Modify the DB_NAME parameter in the initialization parameter file. The startup will result in an error but proceed anyway.
SQL>STARTUP MOUNT
SQL>ALTER SYSTEM SET DB_NAME=SWM SCOPE=SPFILE;
SQL>SHUTDOWN IMMEDIATE
Create a new password file:
$cd /u01/app/oracle/product/10.1.0/db_1/dbs/
$orapwd file=/u01/app/oracle/product/10.1.0/db_1/dbs/orapw password=sys entries=10
If you are using UNIX/Linux simply reset the ORACLE_SID environment variable:
$ORACLE_SID=SWM; export ORACLE_SID
Rename the SPFILE to match the new DBNAME.
If you are using Windows you must recreate the service so the correct name and parameter file are used:
oradim -delete -sid SWDM
oradim -new -sid SWM -intpwd password -startmode a -pfile c:\oracle\920\database\spfileSWM.ora
Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:
$lsnrctl reload
Open the database with RESETLOGS:
SQL>STARTUP MOUNT
SQL>ALTER DATABASE OPEN RESETLOGS;
Backup the database.
==========================================================================
CHANGING DBNAME Only
---------------------
Repeat the process as before except use the following command to start the DBNEWID utility:
$nid TARGET=sys/sys@SWDM DBNAME=swm SETNAME=YES
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>ALTER SYSTEM SET DB_NAME=SWM SCOPE=spfile;
SQL>SHUDOWN IMMEDIATE;
On Windows:
C:\> orapwd file= C:\oracle\product\10.1.0\db_1\database\pwdSWM.ora password=xxxxxxx entries=10
On Unix:
$ orapwd file=/u01/app/oracle/product/10.1.0/db_1/dbs/orapw password=sys entries=10
SQL>STARTUP;
SQL>SHOW PARAMETER DB_NAME
We have changed only the database name, The SETNAME parameter tells the DBNEWID utility to only alter the database name. and
not the database ID, it is not necessary to use the RESETLOGS option when you open the database
Backup the database.
===========================================================================
CHANGING DBID Only
-------------------
Backup the database.
Mount the database after a clean shutdown:
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT
Invoke the DBNEWID utility (nid) from the command line using a user with SYSDBA privilege. Do not specify a new DBNAME:
$nid TARGET=sys/password@SWDM
Shutdown and open the database with RESETLOGS:
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT
SQL>ALTER DATABASE OPEN RESETLOGS;
Backup the database.
Monday, November 05, 2007
Resize undo tablespace
You created undo tablespace using AUTOEXTEND ON MAXSIZE UNLIMITED to avoid error.
Your have just done purging (deleting millions of rows) on your production database and you noticed that your undo tablespace datafile is huge in size.
You tried to use “ALTER DATABASE DATAFILE .. RESIZE”, and this command failed with ORA-3297 error. , i.e.: “file contains ~~ blocks of data beyond requested RESIZE value”.
So you should drop and recreate undo tablespace using the following commands:
1, Connect to Oracle
sqlplus /nolog
connect / as sysdba
2, Find out which undo tablespace is being used:
SQL>select name,value from v$parameter where name in ('undo_management','undo_tablespace');
NAME VALUE
——————- ——————-
undo_management AUTO
undo_tablespace UNDOTBS1
3, Create new undo tablespace UNDOTBS2 with smaller size.
SQL> create undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/edr/undotbs02.dbf' size 500m reuse;
4, Tell Oracle to use new undo tablespace.
SQL> alter system set undo_tablespace=UNDOTBS2;
5, Now you can safely drop and recreate tablespace UNDOTBS1.
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.
6, SQL> CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/edr/undotbs01.dbf' SIZE 250m reuse AUTOEXTEND ON ;
Tablespace created.
7, SQL> alter system set undo_tablespace=UNDOTBS1;
8, Now you can safely drop tablespace UNDOTBS2.
SQL> drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.
Your have just done purging (deleting millions of rows) on your production database and you noticed that your undo tablespace datafile is huge in size.
You tried to use “ALTER DATABASE DATAFILE .. RESIZE”, and this command failed with ORA-3297 error. , i.e.: “file contains ~~ blocks of data beyond requested RESIZE value”.
So you should drop and recreate undo tablespace using the following commands:
1, Connect to Oracle
sqlplus /nolog
connect / as sysdba
2, Find out which undo tablespace is being used:
SQL>select name,value from v$parameter where name in ('undo_management','undo_tablespace');
NAME VALUE
——————- ——————-
undo_management AUTO
undo_tablespace UNDOTBS1
3, Create new undo tablespace UNDOTBS2 with smaller size.
SQL> create undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/edr/undotbs02.dbf' size 500m reuse;
4, Tell Oracle to use new undo tablespace.
SQL> alter system set undo_tablespace=UNDOTBS2;
5, Now you can safely drop and recreate tablespace UNDOTBS1.
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.
6, SQL> CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/edr/undotbs01.dbf' SIZE 250m reuse AUTOEXTEND ON ;
Tablespace created.
7, SQL> alter system set undo_tablespace=UNDOTBS1;
8, Now you can safely drop tablespace UNDOTBS2.
SQL> drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.
Recreate Temp tablespace
You created temp tablespace using AUTOEXTEND ON MAXSIZE UNLIMITED to avoid error.
On your production database you noticed that your temp tablespace datafile is huge in size. So you should drop and recreate temp tablespace using the following commands,
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/app/oracle/oradata/edr/temp02.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
------------------------------------------------------------------------------------------
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/edr/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
------------------------------------------------------------------------------------------
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
On your production database you noticed that your temp tablespace datafile is huge in size. So you should drop and recreate temp tablespace using the following commands,
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/app/oracle/oradata/edr/temp02.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
------------------------------------------------------------------------------------------
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/edr/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
------------------------------------------------------------------------------------------
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
Thursday, October 04, 2007
Installation of Oracle 10G on Solaris 10 -- Short
As the root user,
#groupadd oinstall
#groupadd dba
#useradd -d /oracle -g oinstall -G dba -m -s /bin/ksh oracle
#passwd -r files oracle
--------------------------------------------------------------------------
Edit the /oracle/.profile file and add lines
umask 022
TMP=/tmp
TMPDIR=$TMP
DISPLAY=localhost:0.0
export TMP TMPDIR DISPLAY
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
ORACLE_SID=sol10
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH
--------------------------------------------------------------------------
save and check
# su - oracle
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
$ env
--------------------------------------------------------------------------
As the root user,
#mkdir /u01/app/oracle
#mkdir /u01/app/oracle/product/10.1.0/db_1
#chown -R oracle:oinstall /oracle
#chmod -R 775 /oracle
--------------------------------------------------------------------------
$gunzip solarisx86_DB_10_1_0_3_Disk1.cpio.gz
$cpio -idm < solarisx86_DB_10_1_0_3_Disk1.cpio
--------------------------------------------------------------------------
edit the /etc/user_attr file and add
oracle::::project=oracle
--------------------------------------------------------------------------
save and check
# su - oracle
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
$ id -p
$ prctl -n project.max-shm-memory -i project oracle
--------------------------------------------------------------------------
new terminal session as root
#prctl -n project.max-shm-memory -v 4gb -r -i project oracle
oracle user's session
$ prctl -n project.max-shm-memory -i project oracle
root user's session
#projmod -s -K "project.max-shm-memory=(priv,4gb,deny)" oracle
---------------------------------------------------------------------------
#xhost +
#su - oracle
$/Disk1/runInstaller -ignoreSysPrereqs
......
......
#groupadd oinstall
#groupadd dba
#useradd -d /oracle -g oinstall -G dba -m -s /bin/ksh oracle
#passwd -r files oracle
--------------------------------------------------------------------------
Edit the /oracle/.profile file and add lines
umask 022
TMP=/tmp
TMPDIR=$TMP
DISPLAY=localhost:0.0
export TMP TMPDIR DISPLAY
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
ORACLE_SID=sol10
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH
--------------------------------------------------------------------------
save and check
# su - oracle
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
$ env
--------------------------------------------------------------------------
As the root user,
#mkdir /u01/app/oracle
#mkdir /u01/app/oracle/product/10.1.0/db_1
#chown -R oracle:oinstall /oracle
#chmod -R 775 /oracle
--------------------------------------------------------------------------
$gunzip solarisx86_DB_10_1_0_3_Disk1.cpio.gz
$cpio -idm < solarisx86_DB_10_1_0_3_Disk1.cpio
--------------------------------------------------------------------------
edit the /etc/user_attr file and add
oracle::::project=oracle
--------------------------------------------------------------------------
save and check
# su - oracle
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
$ id -p
$ prctl -n project.max-shm-memory -i project oracle
--------------------------------------------------------------------------
new terminal session as root
#prctl -n project.max-shm-memory -v 4gb -r -i project oracle
oracle user's session
$ prctl -n project.max-shm-memory -i project oracle
root user's session
#projmod -s -K "project.max-shm-memory=(priv,4gb,deny)" oracle
---------------------------------------------------------------------------
#xhost +
#su - oracle
$/Disk1/runInstaller -ignoreSysPrereqs
......
......
Thursday, September 13, 2007
Oracle Installation(9.2.0.1.0) on Red Hat Enterprise Linux 3 and upgrading to 9.2.0.4
In order to install an Oracle9iR2 database on RHEL 3, the "Oracle9iR2 Patch Set 3 9.2.0.4.0" patchset and some other patches must be applied. Some errors can only be fixed by applying the 9.2.0.4 patchset.
Patches required:
p3006854_9204_LINUX.zip
p3095277_9204_LINUX.zip
p3119415_9204_LINUX.zip
1. Install/Check the following RPMs (see Oracle Note:252217.1 for more information):
su - root
rpm -q patch name-->To check
rpm -ivh \
compat-db-4.0.14-5.i386.rpm \
compat-gcc-7.3-2.96.122.i386.rpm \
compat-gcc-c++-7.3-2.96.122.i386.rpm \
compat-libstdc++-7.3-2.96.122.i386.rpm \
compat-libstdc++-devel-7.3-2.96.122.i386.rpm \
openmotif21-2.1.30-8.i386.rpm \
setarch-1.3-1.i386.rpm \
tcl-8.3.5-92.i386.rpm
2. Open a terminal window and log in as the root user.
3. The following local UNIX groups and user must exist on the system:
Groups: oinstall and dba
User: oracle
Now create the two groups oinstall, dba
#groupadd oinstall
#groupadd dba
Create the operating system user oracle:
#useradd -g oinstall -G dba oracle
Enter the following command to set the password of the oracle user:
#passwd oracle
4. Create the directory for the software installation and assign ownership to oracle:oinstall. In the example, you use /u01/app/oracle.
mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app
chmod -R 775 /u01/app
5. With an editor of your choosing, open /home/oracle/.bash_profile and add the entries to the appropriate file.
umask 022
PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
LD_LIBRARY_PATH=/usr/lib:/usr/X11R6/lib
ORACLE_BASE=/u01/app/oracle/
ORACLE_HOME=/u01/app/oracle/product/9.2.0.1.0
#PATH=/u01/app/oracle/product/9.2.0.1.0/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin
export PATH
ORACLE_OWNER=oracle
ORACLE_SID=sid
ORACLE_SID=orcl
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE ORACLE_HOME ORACLE_OWNER ORACLE_SID
export PATH LD_LIBRARY_PATH
CLASSPATH=:/u01/app/oracle/product/9.2.0.1.0/JRE:/u01/app/oracle/product/9.2.0.1.0/jlib:/u01/app/oracle/product/9.2.0.1.0/rdbms/jlib:/u01/app/oracle/product/9.2.0.1.0/network/jlib
export CLASSPATH
LD_ASSUME_KERNEL=2.4.1
export LD_ASSUME_KERNEL
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
6. Configuring Kernel Parameters
See my previous post for the kernel configuration settings.
Kernel Settings
---------
7. If now you execute runInstaller from the Oracle 9iR2 (9.2.0) CD, you will get the following error message:
Error occurred during initialization of VM
Unable to load native library: /tmp/OraInstall2007-8-25_03-14-57PM/jre/lib/i386/libjava.so:
symbol __libc_wait, version GLIBC_2.0 not defined in file libc.so.6 with link time reference
To resolve the __libc_wait symbol issue, download the p3006854_9204 patch p3006854_9204_LINUX.zip from http://metalink.oracle.com.
To apply the patch, run
su - root
# unzip p3006854_9204_LINUX.zip
Archive: p3006854_9204_LINUX.zip
creating: 3006854/
inflating: 3006854/rhel3_pre_install.sh
inflating: 3006854/README.txt
# cd 3006854
# sh rhel3_pre_install.sh
Applying patch...
Patch successfully applied
#
Now runInstaller can be started from the CD:
su - oracle
$ echo $LD_ASSUME_KERNEL # it is important that this variable is set!
2.4.1
$ /mnt/cdrom/runInstaller
You may get the following errors:
Error in invoking target install of makefile /u01/app/oracle/product/9.2.0/network/lib/ins_oemagent.mk.
Click ignore.
This will be fixed by applying the patch 3119415 after the 9.2.0.4 patchset has been applied.
You won't be able to apply the patch 3119415 at this time since the file /u01/app/oracle/oraInventory/ContentsXML/comps.xml doesn't exist yet.
Error in invoking target install of makefile /u01/app/oracle/product/9.2.0/ctx/lib/ins_ctx.mk.
Click ignore.
This is fixed by applying the 9.2.0.4 patchset.
Patching Oracle9iR2 on RH AS 3
To patch Oracle9iR2, download the Oracle 9i Release 2 Patch Set 3 Version 9.2.0.4.0 for Linux x86 from http://metalink.oracle.com.
Copy the downloaded "p3095277_9204_LINUX.zip" file to e.g. /tmp and run the following command:
su - oracle
$ cp p3095277_9204_LINUX.zip /tmp
$ cd /tmp
$ unzip p3095277_9204_LINUX.zip
Archive: p3095277_9204_LINUX.zip
inflating: 9204_lnx32_release.cpio
inflating: README.html
inflating: patchnote.css
$
$ cpio -idmv < 9204_lnx32_release.cpio
Disk1/stage/locks
Disk1/stage/Patches/oracle.apache.isqlplus/9.2.0.4.0/1/DataFiles/bin.1.1.jar
Disk1/stage/Patches/oracle.apache.isqlplus/9.2.0.4.0/1/DataFiles/lib.1.1.jar
...
To patch the runInstaller, run:
su - oracle
$ echo $LD_ASSUME_KERNEL # it is important that this variable is set!
2.4.1
$ cd /tmp/Disk1/
$ ./runInstaller
- Welcome Screen: Click Next
- File Locations: Use default values
- Available Products: Select "Oracle Universial Installer 2.2.0.18.0 !"
- Components Locations: Accept default values and click Next
- Summary: Start the Install
- At the end of the installation, you must exit runInstaller!
To patch Oracle9iR2, run:
su - oracle
$ echo $LD_ASSUME_KERNEL # it is important that this variable is set!
2.4.1
$ cd $ORACLE_HOME/bin
$ ./runInstaller
- Welcome Screen: Click Next
- File Locations: Use default values
- Available Products: Select "Oracle9iR2 Patch Set 3 9.2.0.4.0 !"
- Summary: Start the Install
- At the end of the installation, exit runInstaller
You may get the following error:
Error in invoking target install of makefile /u01/app/oracle/product/9.2.0/network/lib/ins_oemagent.mk.
Click ignore.
This will be fixed by applying the patch 3119415 after the 9.2.0.4 patchset has been applied.
The patch 3119415 cannot be applied while the patch process for the 9.2.0.4 patchset is running.
After the 9.2.0.4 patchset has been applied, download the patch p3119415_9204_LINUX.zip. Also, download the opatch Release 2.2.0 utility from http://metalink.oracle.com.
To install opatch, run:
su - oracle
$ cp p2617419_210_GENERIC.zip /tmp
$ cd /tmp
$ unzip p2617419_210_GENERIC.zip
Before you apply the 3119415 patch, you need to make sure the fuser binary can be found by the oracle user, see the PATH environment variable below. Otherwise the patch can't be applied because the fuser binary is used by opatch.
To apply the 3119415 patch, run
su - oracle
$ unzip p3119415_9204_LINUX.zip
$ cd 3119415
$ export PATH=$PATH:/tmp/OPatch
$ export PATH=$PATH:/sbin # the patch needs "fuser" which is located in /sbin
$ which opatch
/tmp/OPatch/opatch
$ opatch apply
Installation of 9iR2 with all required patches is completed succesfully.
Patches required:
p3006854_9204_LINUX.zip
p3095277_9204_LINUX.zip
p3119415_9204_LINUX.zip
1. Install/Check the following RPMs (see Oracle Note:252217.1 for more information):
su - root
rpm -q patch name-->To check
rpm -ivh \
compat-db-4.0.14-5.i386.rpm \
compat-gcc-7.3-2.96.122.i386.rpm \
compat-gcc-c++-7.3-2.96.122.i386.rpm \
compat-libstdc++-7.3-2.96.122.i386.rpm \
compat-libstdc++-devel-7.3-2.96.122.i386.rpm \
openmotif21-2.1.30-8.i386.rpm \
setarch-1.3-1.i386.rpm \
tcl-8.3.5-92.i386.rpm
2. Open a terminal window and log in as the root user.
3. The following local UNIX groups and user must exist on the system:
Groups: oinstall and dba
User: oracle
Now create the two groups oinstall, dba
#groupadd oinstall
#groupadd dba
Create the operating system user oracle:
#useradd -g oinstall -G dba oracle
Enter the following command to set the password of the oracle user:
#passwd oracle
4. Create the directory for the software installation and assign ownership to oracle:oinstall. In the example, you use /u01/app/oracle.
mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app
chmod -R 775 /u01/app
5. With an editor of your choosing, open /home/oracle/.bash_profile and add the entries to the appropriate file.
umask 022
PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
LD_LIBRARY_PATH=/usr/lib:/usr/X11R6/lib
ORACLE_BASE=/u01/app/oracle/
ORACLE_HOME=/u01/app/oracle/product/9.2.0.1.0
#PATH=/u01/app/oracle/product/9.2.0.1.0/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin
export PATH
ORACLE_OWNER=oracle
ORACLE_SID=sid
ORACLE_SID=orcl
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE ORACLE_HOME ORACLE_OWNER ORACLE_SID
export PATH LD_LIBRARY_PATH
CLASSPATH=:/u01/app/oracle/product/9.2.0.1.0/JRE:/u01/app/oracle/product/9.2.0.1.0/jlib:/u01/app/oracle/product/9.2.0.1.0/rdbms/jlib:/u01/app/oracle/product/9.2.0.1.0/network/jlib
export CLASSPATH
LD_ASSUME_KERNEL=2.4.1
export LD_ASSUME_KERNEL
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
6. Configuring Kernel Parameters
See my previous post for the kernel configuration settings.
Kernel Settings
---------
7. If now you execute runInstaller from the Oracle 9iR2 (9.2.0) CD, you will get the following error message:
Error occurred during initialization of VM
Unable to load native library: /tmp/OraInstall2007-8-25_03-14-57PM/jre/lib/i386/libjava.so:
symbol __libc_wait, version GLIBC_2.0 not defined in file libc.so.6 with link time reference
To resolve the __libc_wait symbol issue, download the p3006854_9204 patch p3006854_9204_LINUX.zip from http://metalink.oracle.com.
To apply the patch, run
su - root
# unzip p3006854_9204_LINUX.zip
Archive: p3006854_9204_LINUX.zip
creating: 3006854/
inflating: 3006854/rhel3_pre_install.sh
inflating: 3006854/README.txt
# cd 3006854
# sh rhel3_pre_install.sh
Applying patch...
Patch successfully applied
#
Now runInstaller can be started from the CD:
su - oracle
$ echo $LD_ASSUME_KERNEL # it is important that this variable is set!
2.4.1
$ /mnt/cdrom/runInstaller
You may get the following errors:
Error in invoking target install of makefile /u01/app/oracle/product/9.2.0/network/lib/ins_oemagent.mk.
Click ignore.
This will be fixed by applying the patch 3119415 after the 9.2.0.4 patchset has been applied.
You won't be able to apply the patch 3119415 at this time since the file /u01/app/oracle/oraInventory/ContentsXML/comps.xml doesn't exist yet.
Error in invoking target install of makefile /u01/app/oracle/product/9.2.0/ctx/lib/ins_ctx.mk.
Click ignore.
This is fixed by applying the 9.2.0.4 patchset.
Patching Oracle9iR2 on RH AS 3
To patch Oracle9iR2, download the Oracle 9i Release 2 Patch Set 3 Version 9.2.0.4.0 for Linux x86 from http://metalink.oracle.com.
Copy the downloaded "p3095277_9204_LINUX.zip" file to e.g. /tmp and run the following command:
su - oracle
$ cp p3095277_9204_LINUX.zip /tmp
$ cd /tmp
$ unzip p3095277_9204_LINUX.zip
Archive: p3095277_9204_LINUX.zip
inflating: 9204_lnx32_release.cpio
inflating: README.html
inflating: patchnote.css
$
$ cpio -idmv < 9204_lnx32_release.cpio
Disk1/stage/locks
Disk1/stage/Patches/oracle.apache.isqlplus/9.2.0.4.0/1/DataFiles/bin.1.1.jar
Disk1/stage/Patches/oracle.apache.isqlplus/9.2.0.4.0/1/DataFiles/lib.1.1.jar
...
To patch the runInstaller, run:
su - oracle
$ echo $LD_ASSUME_KERNEL # it is important that this variable is set!
2.4.1
$ cd /tmp/Disk1/
$ ./runInstaller
- Welcome Screen: Click Next
- File Locations: Use default values
- Available Products: Select "Oracle Universial Installer 2.2.0.18.0 !"
- Components Locations: Accept default values and click Next
- Summary: Start the Install
- At the end of the installation, you must exit runInstaller!
To patch Oracle9iR2, run:
su - oracle
$ echo $LD_ASSUME_KERNEL # it is important that this variable is set!
2.4.1
$ cd $ORACLE_HOME/bin
$ ./runInstaller
- Welcome Screen: Click Next
- File Locations: Use default values
- Available Products: Select "Oracle9iR2 Patch Set 3 9.2.0.4.0 !"
- Summary: Start the Install
- At the end of the installation, exit runInstaller
You may get the following error:
Error in invoking target install of makefile /u01/app/oracle/product/9.2.0/network/lib/ins_oemagent.mk.
Click ignore.
This will be fixed by applying the patch 3119415 after the 9.2.0.4 patchset has been applied.
The patch 3119415 cannot be applied while the patch process for the 9.2.0.4 patchset is running.
After the 9.2.0.4 patchset has been applied, download the patch p3119415_9204_LINUX.zip. Also, download the opatch Release 2.2.0 utility from http://metalink.oracle.com.
To install opatch, run:
su - oracle
$ cp p2617419_210_GENERIC.zip /tmp
$ cd /tmp
$ unzip p2617419_210_GENERIC.zip
Before you apply the 3119415 patch, you need to make sure the fuser binary can be found by the oracle user, see the PATH environment variable below. Otherwise the patch can't be applied because the fuser binary is used by opatch.
To apply the 3119415 patch, run
su - oracle
$ unzip p3119415_9204_LINUX.zip
$ cd 3119415
$ export PATH=$PATH:/tmp/OPatch
$ export PATH=$PATH:/sbin # the patch needs "fuser" which is located in /sbin
$ which opatch
/tmp/OPatch/opatch
$ opatch apply
Installation of 9iR2 with all required patches is completed succesfully.
Tuesday, August 28, 2007
Upgrading 9iR2 to 10gR2 using hotbackup
Upgrading 9iR2 to 10gR2 using hotbackup
Abstract
The document is aimed at describing the step by step procedure that was followed to upgrade a database from Oracle 9iR2 to 10gR2 using hot backup of 9iR2 database. The basis of this upgrade is a hot backup clone.
Case History
The database to be upgraded is about 185GB in size, this activity was tested on a development system to validate the success of this exercise before proceeding with the production environment.
Analysis
Summary
A hot backup was used to create a database on 10gR2 by recreating the controlfile.
Detailed Analysis
1. A password file was initially created in $ORACLE_HOME/dbs of 10gR2 for this particular instance using the orapwd utility. The pfile from the 9i database was used to start the instance by just changing the compatible parameter to 10.2.0 and no other changes were made to the init.ora file since all the deprecated and obsolete parameters will be listed in the alert log once the database is brought up which can be removed later. The instance was now started.
SQL> startup nomount
This step can also be executed as shown below, which is an observation from the alert log when the database failed to open with an error of “ORA-39700: database must be opened with UPGRADE option" preceded with an error of "ORA-00704: bootstrap process failure"
SQL> startup upgrade
2. The next step is to execute the control file recreation script in order to create the controlfile. This is a text controlfile script which was dumped to a trace file from the production environment and updated to reflect the locations of files in the development server.
SQL> !cat control_9_to_10.sql
CREATE CONTROLFILE SET DATABASE "TEN_DB" RESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 '/home/oracle/dev/log1a.rdo' SIZE 200M,
GROUP 2 '/home/oracle/dev/log2a.rdo' SIZE 200M,
GROUP 3 '/home/oracle/dev/log3a.rdo' SIZE 200M,
GROUP 4 '/home/oracle/dev/log4a.rdo' SIZE 200M
DATAFILE
'/home/oracle/dev/data01.dbf',
'/home/oracle/dev/data02.dbf',
'/home/oracle/dev/data03.dbf',
'/home/oracle/dev/users.dbf',
'/home/oracle/dev/tools.dbf',
'/home/oracle/dev/system01.dbf',
'/home/oracle/dev/undotbs01.dbf',
'/home/oracle/dev/undotbs02.dbf',
'/home/oracle/dev/undotbs03.dbf',
'/home/oracle/dev/index01.dbf',
'/home/oracle/dev/index02.dbf',
'/home/oracle/dev/index04.dbf',
'/home/oracle/dev/index05.dbf',
'/home/oracle/dev/index06.dbf',
'/home/oracle/dev/index03.dbf',
'/home/oracle/dev/index07.dbf',
'/home/oracle/dev/data04.dbf',
'/home/oracle/dev/data05.dbf',
'/home/oracle/dev/data06.dbf',
'/home/oracle/dev/data07.dbf',
'/home/oracle/dev/data08.dbf',
'/home/oracle/dev/data09.dbf',
'/home/oracle/dev/data10.dbf',
'/home/oracle/dev/data11.dbf',
'/home/oracle/dev/data12.dbf',
'/home/oracle/dev/data13.dbf',
'/home/oracle/dev/data14.dbf',
'/home/oracle/dev/data15.dbf',
'/home/oracle/dev/data16.dbf',
'/home/oracle/dev/data17.dbf'
CHARACTER SET US7ASCII;
SQL> @control_9_to_10.sql
Control file created.
3. The database was then recovered to the most recent point of time (until the last available archive file). Since the controlfile was recreated so it was very much necessary to use the keywords “using backup controlfile” during recovery. The files marked in bold are the archives which were applied for recovery.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 79118 generated at 08/06/2007 15:43:45 needed for thread 1
ORA-00289: suggestion :
/home/oracle/dev/arch/1_43_629467995.dbf
ORA-00280: change 79118 for thread 1 is in sequence #43
Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/dev/arch/1_43_629467995.dbf
ORA-00279: change 79120 generated at 08/06/2007 15:43:51 needed for thread 1
ORA-00289: suggestion :
/home/oracle/dev/arch/1_44_629467995.dbf
ORA-00280: change 79120 for thread 1 is in sequence #44
ORA-00278: log file '/home/oracle/dev/arch/1_43.dbf' no
longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/dev/arch/1_44_629467995.dbf
ORA-00279: change 79122 generated at 08/06/2007 15:43:51 needed for thread 1
ORA-00289: suggestion :
/home/oracle/dev/arch/1_45_629467995.dbf
ORA-00280: change 79122 for thread 1 is in sequence #45
ORA-00278: log file '/home/oracle/dev/arch/1_44.dbf' no
longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL>
4. This is the most daunting step one would go through, because a normal “alter database open resetlogs” reported an error of "ORA-39700: database must be opened with UPGRADE option" in the alert log and an error of "ORA-01092: ORACLE instance terminated. Disconnection forced" at the SQL prompt. It was improvised from this point that a "startup upgrade" can also be used to start the instance. As for the “ORA-39700” error in alert log, the statement shown below was executed and it surprisingly worked.
SQL> alter database open resetlogs upgrade;
Database altered.
5. Since there is no concept of SYSAUX tablespace in 9i and which is one of the essential tablespaces in 10g, a SYSAUX tablespace was then created manually as shown below.
SQL> create tablespace SYSAUX datafile '/home/oracle/dev/sysaux.dbf' size 500M online permanent extent management local segment space management auto;
Tablespace created.
6. A temporary tablespace was created since it was not included in the controlfile recreation script, and made the default temporary tablespace.
SQL> create temporary tablespace temp tempfile
'/home/oracle/dev/temp01.dbf' size 8000M;
Tablespace created.
SQL> alter database default temporary tablespace temp;
Database altered.
7. Now, When trying to query the dba or v$ views, the database would not respond. When trying to interrupt using the traditional interrupt keys on UNIX "Ctrl+c" an error of ORA-600 was reported at the SQL prompt as shown.
SQL> select file_name from dba_data_files;
ORA-00600: internal error code, arguments: [17069], 0xC00000002B8EDDA0],[], [], [], [], [], []
To start it all over again the database was bounced but it would not come up with a normal "startup" and only accepts "startup upgrade" to come up. This is where it was located from the discussion threads of Metalink that in doing an upgrade from 9i to 10g, the data dictionary needs to be synchronized with the version of the database. The Oracle sever makes a match between the database version and data dictionary every time it opens the database thereby only allowing a “startup upgrade" rather than a normal “startup” to bring the database up.
It was suggested from another discussion thread of Metalink that catupgrd.sql script needed to be executed from 10gR2 $ORACLE_HOME/rdbms/admin to achieve the synchronization. The script ran for a while and completed successfully.
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
The following script was executed for the utility upgrade status check which was successful as well.
SQL> @$ORACLE_HOME/rdbms/admin/utlu102s.sql
The database was bounced yet again and this time it did come up with a normal "startup". The script utlrp.sql was executed a couple of times to recompile all the invalid objects. The global name of the database was then changed to reflect the new name because creating a database with a control file recreation keeps the global_name of the old database unchanged as shown below.
SQL> select * from global_name;
GLOBAL_NAME
-----------------------------------------------------------
NINE_DB.TEST.COM
The global_name was changed by running the following statement which marked the successful completion of upgrade from Oracle 9iR2 to 10gR2.
SQL> alter database rename global_name to TEN_DB.TEST.COM;
Note: If the upgrade is from Oracle 9iR2 to 10gR1 then the synchronization is done by executing u0902000.sql and the utility upgrade status is checked by executing utlu101s.sql from 10gR1 $ORACLE_HOME/rdbms/admin.(Metalink Note: 263809.1)
10gR1 $ORACLE_HOME/rdbms/admin/u0902000.sql --- to upgrade to 10.1.0
which is renamed as catupgrd.sql in 10gR2
10gR1 $ORACLE_HOME/rdbms/admin/utlu101s.sql --- to upgrade to 10.1.0
which is also renamed as utlu102s.sql in 10gR2
Conclusion
A simple concept of cloning a database has been a vital step for the upgrade of an Oracle server version, which not only reduces the downtime for the upgrade but also is achievable with least overhead.
Author: Nawaz Ahmed M.A.S., System Engineer, GAVS Information Services, India. (Metalink Note id: 455990.1)
Abstract
The document is aimed at describing the step by step procedure that was followed to upgrade a database from Oracle 9iR2 to 10gR2 using hot backup of 9iR2 database. The basis of this upgrade is a hot backup clone.
Case History
The database to be upgraded is about 185GB in size, this activity was tested on a development system to validate the success of this exercise before proceeding with the production environment.
Analysis
Summary
A hot backup was used to create a database on 10gR2 by recreating the controlfile.
Detailed Analysis
1. A password file was initially created in $ORACLE_HOME/dbs of 10gR2 for this particular instance using the orapwd utility. The pfile from the 9i database was used to start the instance by just changing the compatible parameter to 10.2.0 and no other changes were made to the init.ora file since all the deprecated and obsolete parameters will be listed in the alert log once the database is brought up which can be removed later. The instance was now started.
SQL> startup nomount
This step can also be executed as shown below, which is an observation from the alert log when the database failed to open with an error of “ORA-39700: database must be opened with UPGRADE option" preceded with an error of "ORA-00704: bootstrap process failure"
SQL> startup upgrade
2. The next step is to execute the control file recreation script in order to create the controlfile. This is a text controlfile script which was dumped to a trace file from the production environment and updated to reflect the locations of files in the development server.
SQL> !cat control_9_to_10.sql
CREATE CONTROLFILE SET DATABASE "TEN_DB" RESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 '/home/oracle/dev/log1a.rdo' SIZE 200M,
GROUP 2 '/home/oracle/dev/log2a.rdo' SIZE 200M,
GROUP 3 '/home/oracle/dev/log3a.rdo' SIZE 200M,
GROUP 4 '/home/oracle/dev/log4a.rdo' SIZE 200M
DATAFILE
'/home/oracle/dev/data01.dbf',
'/home/oracle/dev/data02.dbf',
'/home/oracle/dev/data03.dbf',
'/home/oracle/dev/users.dbf',
'/home/oracle/dev/tools.dbf',
'/home/oracle/dev/system01.dbf',
'/home/oracle/dev/undotbs01.dbf',
'/home/oracle/dev/undotbs02.dbf',
'/home/oracle/dev/undotbs03.dbf',
'/home/oracle/dev/index01.dbf',
'/home/oracle/dev/index02.dbf',
'/home/oracle/dev/index04.dbf',
'/home/oracle/dev/index05.dbf',
'/home/oracle/dev/index06.dbf',
'/home/oracle/dev/index03.dbf',
'/home/oracle/dev/index07.dbf',
'/home/oracle/dev/data04.dbf',
'/home/oracle/dev/data05.dbf',
'/home/oracle/dev/data06.dbf',
'/home/oracle/dev/data07.dbf',
'/home/oracle/dev/data08.dbf',
'/home/oracle/dev/data09.dbf',
'/home/oracle/dev/data10.dbf',
'/home/oracle/dev/data11.dbf',
'/home/oracle/dev/data12.dbf',
'/home/oracle/dev/data13.dbf',
'/home/oracle/dev/data14.dbf',
'/home/oracle/dev/data15.dbf',
'/home/oracle/dev/data16.dbf',
'/home/oracle/dev/data17.dbf'
CHARACTER SET US7ASCII;
SQL> @control_9_to_10.sql
Control file created.
3. The database was then recovered to the most recent point of time (until the last available archive file). Since the controlfile was recreated so it was very much necessary to use the keywords “using backup controlfile” during recovery. The files marked in bold are the archives which were applied for recovery.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 79118 generated at 08/06/2007 15:43:45 needed for thread 1
ORA-00289: suggestion :
/home/oracle/dev/arch/1_43_629467995.dbf
ORA-00280: change 79118 for thread 1 is in sequence #43
Specify log: {
/home/oracle/dev/arch/1_43_629467995.dbf
ORA-00279: change 79120 generated at 08/06/2007 15:43:51 needed for thread 1
ORA-00289: suggestion :
/home/oracle/dev/arch/1_44_629467995.dbf
ORA-00280: change 79120 for thread 1 is in sequence #44
ORA-00278: log file '/home/oracle/dev/arch/1_43.dbf' no
longer needed for this recovery
Specify log: {
/home/oracle/dev/arch/1_44_629467995.dbf
ORA-00279: change 79122 generated at 08/06/2007 15:43:51 needed for thread 1
ORA-00289: suggestion :
/home/oracle/dev/arch/1_45_629467995.dbf
ORA-00280: change 79122 for thread 1 is in sequence #45
ORA-00278: log file '/home/oracle/dev/arch/1_44.dbf' no
longer needed for this recovery
Specify log: {
CANCEL
Media recovery cancelled.
SQL>
4. This is the most daunting step one would go through, because a normal “alter database open resetlogs” reported an error of "ORA-39700: database must be opened with UPGRADE option" in the alert log and an error of "ORA-01092: ORACLE instance terminated. Disconnection forced" at the SQL prompt. It was improvised from this point that a "startup upgrade" can also be used to start the instance. As for the “ORA-39700” error in alert log, the statement shown below was executed and it surprisingly worked.
SQL> alter database open resetlogs upgrade;
Database altered.
5. Since there is no concept of SYSAUX tablespace in 9i and which is one of the essential tablespaces in 10g, a SYSAUX tablespace was then created manually as shown below.
SQL> create tablespace SYSAUX datafile '/home/oracle/dev/sysaux.dbf' size 500M online permanent extent management local segment space management auto;
Tablespace created.
6. A temporary tablespace was created since it was not included in the controlfile recreation script, and made the default temporary tablespace.
SQL> create temporary tablespace temp tempfile
'/home/oracle/dev/temp01.dbf' size 8000M;
Tablespace created.
SQL> alter database default temporary tablespace temp;
Database altered.
7. Now, When trying to query the dba or v$ views, the database would not respond. When trying to interrupt using the traditional interrupt keys on UNIX "Ctrl+c" an error of ORA-600 was reported at the SQL prompt as shown.
SQL> select file_name from dba_data_files;
ORA-00600: internal error code, arguments: [17069], 0xC00000002B8EDDA0],[], [], [], [], [], []
To start it all over again the database was bounced but it would not come up with a normal "startup" and only accepts "startup upgrade" to come up. This is where it was located from the discussion threads of Metalink that in doing an upgrade from 9i to 10g, the data dictionary needs to be synchronized with the version of the database. The Oracle sever makes a match between the database version and data dictionary every time it opens the database thereby only allowing a “startup upgrade" rather than a normal “startup” to bring the database up.
It was suggested from another discussion thread of Metalink that catupgrd.sql script needed to be executed from 10gR2 $ORACLE_HOME/rdbms/admin to achieve the synchronization. The script ran for a while and completed successfully.
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
The following script was executed for the utility upgrade status check which was successful as well.
SQL> @$ORACLE_HOME/rdbms/admin/utlu102s.sql
The database was bounced yet again and this time it did come up with a normal "startup". The script utlrp.sql was executed a couple of times to recompile all the invalid objects. The global name of the database was then changed to reflect the new name because creating a database with a control file recreation keeps the global_name of the old database unchanged as shown below.
SQL> select * from global_name;
GLOBAL_NAME
-----------------------------------------------------------
NINE_DB.TEST.COM
The global_name was changed by running the following statement which marked the successful completion of upgrade from Oracle 9iR2 to 10gR2.
SQL> alter database rename global_name to TEN_DB.TEST.COM;
Note: If the upgrade is from Oracle 9iR2 to 10gR1 then the synchronization is done by executing u0902000.sql and the utility upgrade status is checked by executing utlu101s.sql from 10gR1 $ORACLE_HOME/rdbms/admin.(Metalink Note: 263809.1)
10gR1 $ORACLE_HOME/rdbms/admin/u0902000.sql --- to upgrade to 10.1.0
which is renamed as catupgrd.sql in 10gR2
10gR1 $ORACLE_HOME/rdbms/admin/utlu101s.sql --- to upgrade to 10.1.0
which is also renamed as utlu102s.sql in 10gR2
Conclusion
A simple concept of cloning a database has been a vital step for the upgrade of an Oracle server version, which not only reduces the downtime for the upgrade but also is achievable with least overhead.
Author: Nawaz Ahmed M.A.S., System Engineer, GAVS Information Services, India. (Metalink Note id: 455990.1)
Monday, August 27, 2007
Configuration of kernel parameters
Configuring Kernel Parameters
If the value of any kernel parameter is different from the recommended value, then complete the following procedure:
Using any text editor, create or edit the /etc/sysctl.conf file, and add or edit lines similar to the following:
Note:
Include lines only for the kernel parameter values that you want to change. For the semaphore parameters (kernel.sem), you must specify all four values. However, if any of the current values are larger than the recommended value, then specify the larger value.
fs.file-max = 512 * PROCESSES
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144
Note: If the current value for any parameter is higher than the value listed in this table, then do not change the value of that parameter.
By specifying the values in the /etc/sysctl.conf file, they persist when you restart the system.
Enter the following command to change the current values of the kernel parameters:
# /sbin/sysctl -p
Setting Shell Limits for the oracle User
To improve the performance of the software on Linux systems, you must increase the following shell limits for the oracle user:
Shell Limit Item in limits.conf Hard Limit
Maximum number of open file descriptors nofile 65536
Maximum number of processes available to a single user nproc 16384
To increase the shell limits:
Add the following lines to the /etc/security/limits.conf file:
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
Add or edit the following line in the /etc/pam.d/login file, if it does not already exist:
session required /lib/security/pam_limits.so
session required pam_limits.so
Note:
For 64-bit platforms, the following entry is not required in the /etc/pam.d/login file:
session required /lib/security/pam_limits.so
Depending on the oracle user's default shell, make the following changes to the default shell start-up file:
For the Bourne, Bash, or Korn shell, add the following lines to the /etc/profile file (or the file on SUSE systems)/etc/profile.local:
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
For the C shell (csh or tcsh), add the following lines to the /etc/csh.login file (or the file on SUSE systems)/etc/csh.login.local:
if ( $USER == "oracle" ) then
limit maxproc 16384
limit descriptors 65536
endif
If the value of any kernel parameter is different from the recommended value, then complete the following procedure:
Using any text editor, create or edit the /etc/sysctl.conf file, and add or edit lines similar to the following:
Note:
Include lines only for the kernel parameter values that you want to change. For the semaphore parameters (kernel.sem), you must specify all four values. However, if any of the current values are larger than the recommended value, then specify the larger value.
fs.file-max = 512 * PROCESSES
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144
Note: If the current value for any parameter is higher than the value listed in this table, then do not change the value of that parameter.
By specifying the values in the /etc/sysctl.conf file, they persist when you restart the system.
Enter the following command to change the current values of the kernel parameters:
# /sbin/sysctl -p
Setting Shell Limits for the oracle User
To improve the performance of the software on Linux systems, you must increase the following shell limits for the oracle user:
Shell Limit Item in limits.conf Hard Limit
Maximum number of open file descriptors nofile 65536
Maximum number of processes available to a single user nproc 16384
To increase the shell limits:
Add the following lines to the /etc/security/limits.conf file:
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
Add or edit the following line in the /etc/pam.d/login file, if it does not already exist:
session required /lib/security/pam_limits.so
session required pam_limits.so
Note:
For 64-bit platforms, the following entry is not required in the /etc/pam.d/login file:
session required /lib/security/pam_limits.so
Depending on the oracle user's default shell, make the following changes to the default shell start-up file:
For the Bourne, Bash, or Korn shell, add the following lines to the /etc/profile file (or the file on SUSE systems)/etc/profile.local:
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
For the C shell (csh or tcsh), add the following lines to the /etc/csh.login file (or the file on SUSE systems)/etc/csh.login.local:
if ( $USER == "oracle" ) then
limit maxproc 16384
limit descriptors 65536
endif
Monday, August 13, 2007
Scheduling backup using Crontab in Linux
Edit the crontab using following command and add the line as below:
$crontab -e
0 06,18 * * * sh /home/oracle/Desktop/bkup.sh
Scheduling backup twice a day at 6 in the morning and 6 in the evening.
Contents of bkup.sh
-------------------
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=edr
mv /home/oracle/edrbkup/old_edr.dmp /home/oracle/edrbkup/old_bkup/
mv /home/oracle/edrbkup/old_edr.log /home/oracle/edrbkup/old_bkup/
mv /home/oracle/edrbkup/edr.dmp /home/oracle/edrbkup/old_edr.dmp
mv /home/oracle/edrbkup/edr.log /home/oracle/edrbkup/old_edr.log
exp edr/regp file=/home/oracle/edrbkup/edr.dmp log=/home/oracle/edrbkup/edr.log owner=edr buffer=200000
This script will automatically clean the old backup and maintains three backups' at any given point of time.
cron commands:
$crontab -l To list the cron jobs scheduled
$crontab -r To remove the cron job
$crontab -e To edit/schedule cron jobs
General Overview:
Crontab Environment
cron invokes the command from the user's HOME directory with the shell, /usr/bin/sh).
cron supplies a default environment for every shell, defining:
HOME=user's-home-directory
LOGNAME=user's-login-id
PATH=/usr/bin:/usr/sbin:.
SHELL=/usr/bin/sh
Users who desire to have their .profile executed must explicitly do so in the crontab entry or in a script called by the entry.
* * * * * command to be executed
- - - - -
| | | | |
| | | | +----- day of week (0 - 6) (sunday = 0)
| | | +------- month (1 - 12)
| | +--------- day of month (1 - 31)
| +----------- hour (0 - 23)
+------------- min (0 - 59)
Hope this may help you.
$crontab -e
0 06,18 * * * sh /home/oracle/Desktop/bkup.sh
Scheduling backup twice a day at 6 in the morning and 6 in the evening.
Contents of bkup.sh
-------------------
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=edr
mv /home/oracle/edrbkup/old_edr.dmp /home/oracle/edrbkup/old_bkup/
mv /home/oracle/edrbkup/old_edr.log /home/oracle/edrbkup/old_bkup/
mv /home/oracle/edrbkup/edr.dmp /home/oracle/edrbkup/old_edr.dmp
mv /home/oracle/edrbkup/edr.log /home/oracle/edrbkup/old_edr.log
exp edr/regp file=/home/oracle/edrbkup/edr.dmp log=/home/oracle/edrbkup/edr.log owner=edr buffer=200000
This script will automatically clean the old backup and maintains three backups' at any given point of time.
cron commands:
$crontab -l To list the cron jobs scheduled
$crontab -r To remove the cron job
$crontab -e To edit/schedule cron jobs
General Overview:
Crontab Environment
cron invokes the command from the user's HOME directory with the shell, /usr/bin/sh).
cron supplies a default environment for every shell, defining:
HOME=user's-home-directory
LOGNAME=user's-login-id
PATH=/usr/bin:/usr/sbin:.
SHELL=/usr/bin/sh
Users who desire to have their .profile executed must explicitly do so in the crontab entry or in a script called by the entry.
* * * * * command to be executed
- - - - -
| | | | |
| | | | +----- day of week (0 - 6) (sunday = 0)
| | | +------- month (1 - 12)
| | +--------- day of month (1 - 31)
| +----------- hour (0 - 23)
+------------- min (0 - 59)
Hope this may help you.
Thursday, August 09, 2007
Creating a Samba Share on a Linux box for a Windows box in network
[root@oraclesrvr ~]# mkdir /regp
[root@oraclesrvr ~]# useradd regpadmin
[root@oraclesrvr ~]# useradd regpuser
[root@oraclesrvr ~]# passwd regpadmin
[root@oraclesrvr ~]# passwd regpuser
[root@oraclesrvr ~]# chown -R regpadmin:regpadmin /regp
[root@oraclesrvr ~]# chmod -R 777 /regp
[root@oraclesrvr ~]# vi /etc/samba/smb.conf
Edit the following lines which are present at the end of file accordingly:
[regp]
comment = regp user stuff
path = /regp/
valid users = regpadmin regpuser
public = no
writable = yes
printable = no
create mask = 0765
:wq
[root@oraclesrvr ~]# cat /etc/passwd | mksmbpasswd.sh > /etc/samba/smbpasswd
[root@oraclesrvr ~]# chmod 600 /etc/samba/smbpasswd
[root@oraclesrvr ~]# smbpasswd regpadmin
[root@oraclesrvr ~]# smbpasswd regpuser
[root@oraclesrvr ~]# service smb restart
Now connect to the linux box from any windows machine in same network and try to create,modify or delete any file in the share created by you.
These permissions can be set according to your requirements.
[root@oraclesrvr ~]# useradd regpadmin
[root@oraclesrvr ~]# useradd regpuser
[root@oraclesrvr ~]# passwd regpadmin
[root@oraclesrvr ~]# passwd regpuser
[root@oraclesrvr ~]# chown -R regpadmin:regpadmin /regp
[root@oraclesrvr ~]# chmod -R 777 /regp
[root@oraclesrvr ~]# vi /etc/samba/smb.conf
Edit the following lines which are present at the end of file accordingly:
[regp]
comment = regp user stuff
path = /regp/
valid users = regpadmin regpuser
public = no
writable = yes
printable = no
create mask = 0765
:wq
[root@oraclesrvr ~]# cat /etc/passwd | mksmbpasswd.sh > /etc/samba/smbpasswd
[root@oraclesrvr ~]# chmod 600 /etc/samba/smbpasswd
[root@oraclesrvr ~]# smbpasswd regpadmin
[root@oraclesrvr ~]# smbpasswd regpuser
[root@oraclesrvr ~]# service smb restart
Now connect to the linux box from any windows machine in same network and try to create,modify or delete any file in the share created by you.
These permissions can be set according to your requirements.
Wednesday, August 01, 2007
Very Large Memory
Creation of Database With Very Large Memory
Once the Oracle software is installed successfully, then the next major step is to create the database:
This document deals with database with Very Large Memory option i.e., allocating more than 1.7GB of SGA on a 32 bit Windows server.
In Windows 32 bit architechture(x86) there is limit for 2GB of memory usage that Windows will allow for any single process to consume.
And the databases in which a large amount of i/o takesplace needs much more memory than this limit. So, to allocate and allow oracle to use more than 2GB of memory we use VLM.
1. Open your boot.ini file and add /PAE and /3GB switches.
2. Install the Oracle Software.
3. Open the regedit file
start->run->regedit->find ORACLE_HOME key this will be in HKEY_LOCAL_MACHINE-->software-->oracle-->key_oracle_home(what ever your home is)
in the right hand pane right click and create a new string value with name AWE_WINDOW_MEMORY
Then double click thih new string and give the vaule in bytes that you require.
By default the value of this string is 1GB.
4. Restart the server to make this changes take effect.
5. create the database with memory greater than the 1.7GB SGA.
While allocating the SGA parameter values take care that you give the following parameter values:
Parameter NAME VALUE
-------------- -----
db_cache_size =0
db_2k_cahe_size =null
db_4k_cahe_size =null
db_8k_cahe_size =null
db_16k_cahe_size =null
db_32k_cahe_size =null
db_cache_advice =OFF
db_block_buffers =262144(2GB) i.e (262144*8192)
db_block_size =8192
shared_pool_size =600M
large_pool_size =48M
java_pool_size =24M
pga_aggregate_target =700M
sga_target =0
used_indirect_buffers=TRUE
Note:db-block_buffres is calculated with the following formula(max that can be allocated):
==>Your Server Physical Ram - 4GB + AWE_WINDOW_MEMORY(1GB)
So for example if your System Physical RAM is 9GB, then according to formula:
9-4+1=6GB
You can allocate upto 6GB value for your db_block_buffers.
Once the Oracle software is installed successfully, then the next major step is to create the database:
This document deals with database with Very Large Memory option i.e., allocating more than 1.7GB of SGA on a 32 bit Windows server.
In Windows 32 bit architechture(x86) there is limit for 2GB of memory usage that Windows will allow for any single process to consume.
And the databases in which a large amount of i/o takesplace needs much more memory than this limit. So, to allocate and allow oracle to use more than 2GB of memory we use VLM.
1. Open your boot.ini file and add /PAE and /3GB switches.
2. Install the Oracle Software.
3. Open the regedit file
start->run->regedit->find ORACLE_HOME key this will be in HKEY_LOCAL_MACHINE-->software-->oracle-->key_oracle_home(what ever your home is)
in the right hand pane right click and create a new string value with name AWE_WINDOW_MEMORY
Then double click thih new string and give the vaule in bytes that you require.
By default the value of this string is 1GB.
4. Restart the server to make this changes take effect.
5. create the database with memory greater than the 1.7GB SGA.
While allocating the SGA parameter values take care that you give the following parameter values:
Parameter NAME VALUE
-------------- -----
db_cache_size =0
db_2k_cahe_size =null
db_4k_cahe_size =null
db_8k_cahe_size =null
db_16k_cahe_size =null
db_32k_cahe_size =null
db_cache_advice =OFF
db_block_buffers =262144(2GB) i.e (262144*8192)
db_block_size =8192
shared_pool_size =600M
large_pool_size =48M
java_pool_size =24M
pga_aggregate_target =700M
sga_target =0
used_indirect_buffers=TRUE
Note:db-block_buffres is calculated with the following formula(max that can be allocated):
==>Your Server Physical Ram - 4GB + AWE_WINDOW_MEMORY(1GB)
So for example if your System Physical RAM is 9GB, then according to formula:
9-4+1=6GB
You can allocate upto 6GB value for your db_block_buffers.
Oracle 10G Installation on Red Hat Linux
First Install Linux with all the rpms and packages required.
1. Create Groups oinstall & dba, and oracle user as follows:
#groupadd oinstall
#groupadd dba
#useradd -g oinstall -G dba oracle
#passwd oracle
give a new password
2. Edit the .bash_profile file of oracle user and add the following lines as follows:
#vi /home/oracle/.bash_profile
umask 022
PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
LD_LIBRARY_PATH=/usr/lib:/usr/X11R6/lib
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
ORACLE_SID='SERVICE_NAME'
PATH=$ORACLE_HOME/bin:$PATH
export PATH LD_LIBRARY_PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID
:wq(save and exit)
3. Then run the .bash_profile to make the current changes effect
#. .bash_profile
Create the directory for the software installation and assign ownership to oracle:oinstall. In the example, you use /u01/app/oracle.
#mkdir -p /u01/app/oracle
#mkdir -p /u01/app/oracle/product/10.1.0/db_1
Then change the permissions accordingly
#chown -R oracle:oinstall /u01/app
#chmod -R 775 /u01/app
4. Edit the /etc/inittab file and change the runlevel from 5 to 3.
Note: Not required if using Oracle's Enterprise Linux.
5. If your OS is greater than RHEL 3, then the Oracle Universal Installer does not recognize the OS, change the version to 3 as follows:
#cp /etc/redhat-release /etc/redhat-release.orig
#vi /etc/redhat-release
#And replace the line present in the file with
Red Hat Enterprise Linux AS release 3 (Taroon)
:wq
Once the installation is completed replace the orignal file back.
#cp /etc/redhat-release.orig /etc/redhat-release
overwrite:yes
6. Change the Kernel Parameters according to your Hardware:
#vi /etc/sysctl.conf
kernel.sem= 250 32000 100 128
kernel.shmmax = 2147483648
kernal.shmmni = 4096
kernel.shmall = 262144000
fs.file-max= 1024
net.ipv4.ip_local_port_range= 1024 65000
:wq
Note: If any of the value is more than the value given above don't change those values.
7. To make the changes effect immediately without reboot give the following command:
#/sbin/sysctl -p
8. REBOOT(init 6) the system and login as oracle User:
9. You need to install Oracle in the GUI mode so change the display mode to GUI by giving followinh command:
$startx
10. Mount the media disk which contains the Oracle 10G software:
Note: It is recommended that you copy the contents of the CD onto the Filesystem and start the installation:
$cp /mnt/disk1/*.* /home/oracle/oracle10g
Give total permissions on the newly created folder oracle10g to oracle user
$chmod -R 777 /home/oracle/oracle10g
11. Then Start the installation as follows:
$sh /home/oracle/oracle10g/runinstaller.sh
The OUI is started and continue Installation of Oracle same as in Windows:
next>next>next>.....................................Finish.
Inbetween it prompts you to run two scripts as root
DON'T close the current window until you run the respected script as root in a new terminal.
1st script:
#root>sh /u01/app/oracle/oraInventory/orainstall.sh
After executing this script as root go to the previous screen and press the continue tab
2nd script:
#root> sh /u01/app/oracle/product/10.1.0/db_1/root.sh
After executing this script as root go to the previous screen and press the OK tab.
Your Installation of Oracle 10G on Linux is completed successfully(hopefully).
1. Create Groups oinstall & dba, and oracle user as follows:
#groupadd oinstall
#groupadd dba
#useradd -g oinstall -G dba oracle
#passwd oracle
give a new password
2. Edit the .bash_profile file of oracle user and add the following lines as follows:
#vi /home/oracle/.bash_profile
umask 022
PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
LD_LIBRARY_PATH=/usr/lib:/usr/X11R6/lib
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
ORACLE_SID='SERVICE_NAME'
PATH=$ORACLE_HOME/bin:$PATH
export PATH LD_LIBRARY_PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID
:wq(save and exit)
3. Then run the .bash_profile to make the current changes effect
#. .bash_profile
Create the directory for the software installation and assign ownership to oracle:oinstall. In the example, you use /u01/app/oracle.
#mkdir -p /u01/app/oracle
#mkdir -p /u01/app/oracle/product/10.1.0/db_1
Then change the permissions accordingly
#chown -R oracle:oinstall /u01/app
#chmod -R 775 /u01/app
4. Edit the /etc/inittab file and change the runlevel from 5 to 3.
Note: Not required if using Oracle's Enterprise Linux.
5. If your OS is greater than RHEL 3, then the Oracle Universal Installer does not recognize the OS, change the version to 3 as follows:
#cp /etc/redhat-release /etc/redhat-release.orig
#vi /etc/redhat-release
#And replace the line present in the file with
Red Hat Enterprise Linux AS release 3 (Taroon)
:wq
Once the installation is completed replace the orignal file back.
#cp /etc/redhat-release.orig /etc/redhat-release
overwrite:yes
6. Change the Kernel Parameters according to your Hardware:
#vi /etc/sysctl.conf
kernel.sem= 250 32000 100 128
kernel.shmmax = 2147483648
kernal.shmmni = 4096
kernel.shmall = 262144000
fs.file-max= 1024
net.ipv4.ip_local_port_range= 1024 65000
:wq
Note: If any of the value is more than the value given above don't change those values.
7. To make the changes effect immediately without reboot give the following command:
#/sbin/sysctl -p
8. REBOOT(init 6) the system and login as oracle User:
9. You need to install Oracle in the GUI mode so change the display mode to GUI by giving followinh command:
$startx
10. Mount the media disk which contains the Oracle 10G software:
Note: It is recommended that you copy the contents of the CD onto the Filesystem and start the installation:
$cp /mnt/disk1/*.* /home/oracle/oracle10g
Give total permissions on the newly created folder oracle10g to oracle user
$chmod -R 777 /home/oracle/oracle10g
11. Then Start the installation as follows:
$sh /home/oracle/oracle10g/runinstaller.sh
The OUI is started and continue Installation of Oracle same as in Windows:
next>next>next>.....................................Finish.
Inbetween it prompts you to run two scripts as root
DON'T close the current window until you run the respected script as root in a new terminal.
1st script:
#root>sh /u01/app/oracle/oraInventory/orainstall.sh
After executing this script as root go to the previous screen and press the continue tab
2nd script:
#root> sh /u01/app/oracle/product/10.1.0/db_1/root.sh
After executing this script as root go to the previous screen and press the OK tab.
Your Installation of Oracle 10G on Linux is completed successfully(hopefully).
Tuesday, July 31, 2007
Oracle DB Auto Start / Shutdown
How to Configure a Linux x86 Server for Oracle DB Auto Start / Shutdown
Applies to: Oracle Server - Enterprise Edition - Version: From 8.1.7.4 to 10.1.0.3 on Linux x86(32 Bit)
The Database server software provides the two scripts to configure automatic DB startup/shutdown with the server machine.
They are
$ORACLE_HOME/bin/dbstart
$ORACLE_HOME/bin/dbshut
We need to call these scripts from the unix start/shutdown scripts (rc0.d / rc1.d etc.)
Step - A:
Check the oratab file in /etc/oratab or /var/opt/oracle/oratab
This should have the entry for the DB we are dealing with, with a value Y, like:
$ORACLE_SID:$ORACLE_HOME:Y
Step - B:
Login to root. Create a new file with name like dbora and
Save the following file in /etc/init.d/
Please note that /etc/init.d is RedHat specific.
Change the permissions accordingly
#chmod 755 /etc/init.d/dbora
-rwxr-xr-x 1 root root 1412 Aug 27 19:14 dbora
Mention the correct ORA_OWNER and ORA_HOME in the dbora
------------------ Edit dbora ---------------------------------
#! /bin/bash
#
# oracle Start/Stop the Databases...
#
# chkconfig: 2345 99 10
#
# processname: oracle
# config: /etc/oratab
# pidfile: /var/run/oracle.pid
# Source function library.
. /etc/init.d/functions
RETVAL=0
ORA_OWNER="oracle"
ORA_HOME="/u01/app/oracle/product/10.1.0/db_1"
# See how we were called.
prog="oracle"
start() {
echo -n $"Starting $prog: "
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart"
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/dbora
return $RETVAL
}
stop() {
echo -n $"Stopping $prog: "
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut"
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
}
restart() {
stop
start
}
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
*)
echo $"Usage: $0 {start|stop|restart}"
exit 1
esac
exit $?
------------------ Save and exit(wq) dbora ---------------------------------
Step - C: Login as root run the following:
# cd /sbin
# chkconfig --add dbora
This will create a system service viz. dbora
# chkconfig --list
This also creates the following files:
/etc/rc2.d/S99dbora ( calls $ORACLE_HOME/bin/dbstart )
/etc/rc3.d/S99dbora ( calls $ORACLE_HOME/bin/dbstart )
/etc/rc4.d/S99dbora ( calls $ORACLE_HOME/bin/dbstart )
/etc/rc5.d/S99dbora ( calls $ORACLE_HOME/bin/dbstart )
/etc/rc0.d/K10dbora ( calls $ORACLE_HOME/bin/dbshut )
/etc/rc1.d/K10dbora ( calls $ORACLE_HOME/bin/dbshut )
/etc/rc6.d/K10dbora ( calls $ORACLE_HOME/bin/dbshut )
chkconfig refers the " # chkconfig: 2345 99 10 " from dbora.
This signifies that the service has start run level set to 2, 3, 4 and 5.Stop run level set to 0, 1 and 6. And the start priority should be 99 and stop priority be 10.
If the version is Red Hat 3 ES or more than the dbora file needs two lines of comments.
Each service which should be manageable by chkconfig needs two or more commented lines
added to its init.d script. The second line contains a description for the service,
and may be extended across multiple lines with backslash continuation.
For example,
# chkconfig: 2345 99 10
# description: Saves and restores system entropy pool for higher quality random number generation
Step - D: Reboot the Linux box and check
In some cases for 9.2.0 we also had to copy the init file for SID "test" from /u01/app/oracle/admin/test/pfile to $ORACLE_HOME/dbs to get dbstart and dbshut working:
cp /u01/app/oracle/admin/test/pfile/inittest.ora.642002224936 $ORACLE_HOME/dbs/inittest.ora
But first make sure if your init file already exists in $ORACLE_HOME/dbs!
Note: An article from Metalink customized for my environment. May help you.
Applies to: Oracle Server - Enterprise Edition - Version: From 8.1.7.4 to 10.1.0.3 on Linux x86(32 Bit)
The Database server software provides the two scripts to configure automatic DB startup/shutdown with the server machine.
They are
$ORACLE_HOME/bin/dbstart
$ORACLE_HOME/bin/dbshut
We need to call these scripts from the unix start/shutdown scripts (rc0.d / rc1.d etc.)
Step - A:
Check the oratab file in /etc/oratab or /var/opt/oracle/oratab
This should have the entry for the DB we are dealing with, with a value Y, like:
$ORACLE_SID:$ORACLE_HOME:Y
Step - B:
Login to root. Create a new file with name like dbora and
Save the following file in /etc/init.d/
Please note that /etc/init.d is RedHat specific.
Change the permissions accordingly
#chmod 755 /etc/init.d/dbora
-rwxr-xr-x 1 root root 1412 Aug 27 19:14 dbora
Mention the correct ORA_OWNER and ORA_HOME in the dbora
------------------ Edit dbora ---------------------------------
#! /bin/bash
#
# oracle Start/Stop the Databases...
#
# chkconfig: 2345 99 10
#
# processname: oracle
# config: /etc/oratab
# pidfile: /var/run/oracle.pid
# Source function library.
. /etc/init.d/functions
RETVAL=0
ORA_OWNER="oracle"
ORA_HOME="/u01/app/oracle/product/10.1.0/db_1"
# See how we were called.
prog="oracle"
start() {
echo -n $"Starting $prog: "
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart"
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/dbora
return $RETVAL
}
stop() {
echo -n $"Stopping $prog: "
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut"
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
}
restart() {
stop
start
}
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
*)
echo $"Usage: $0 {start|stop|restart}"
exit 1
esac
exit $?
------------------ Save and exit(wq) dbora ---------------------------------
Step - C: Login as root run the following:
# cd /sbin
# chkconfig --add dbora
This will create a system service viz. dbora
# chkconfig --list
This also creates the following files:
/etc/rc2.d/S99dbora ( calls $ORACLE_HOME/bin/dbstart )
/etc/rc3.d/S99dbora ( calls $ORACLE_HOME/bin/dbstart )
/etc/rc4.d/S99dbora ( calls $ORACLE_HOME/bin/dbstart )
/etc/rc5.d/S99dbora ( calls $ORACLE_HOME/bin/dbstart )
/etc/rc0.d/K10dbora ( calls $ORACLE_HOME/bin/dbshut )
/etc/rc1.d/K10dbora ( calls $ORACLE_HOME/bin/dbshut )
/etc/rc6.d/K10dbora ( calls $ORACLE_HOME/bin/dbshut )
chkconfig refers the " # chkconfig: 2345 99 10 " from dbora.
This signifies that the service has start run level set to 2, 3, 4 and 5.Stop run level set to 0, 1 and 6. And the start priority should be 99 and stop priority be 10.
If the version is Red Hat 3 ES or more than the dbora file needs two lines of comments.
Each service which should be manageable by chkconfig needs two or more commented lines
added to its init.d script. The second line contains a description for the service,
and may be extended across multiple lines with backslash continuation.
For example,
# chkconfig: 2345 99 10
# description: Saves and restores system entropy pool for higher quality random number generation
Step - D: Reboot the Linux box and check
In some cases for 9.2.0 we also had to copy the init file for SID "test" from /u01/app/oracle/admin/test/pfile to $ORACLE_HOME/dbs to get dbstart and dbshut working:
cp /u01/app/oracle/admin/test/pfile/inittest.ora.642002224936 $ORACLE_HOME/dbs/inittest.ora
But first make sure if your init file already exists in $ORACLE_HOME/dbs!
Note: An article from Metalink customized for my environment. May help you.
Saturday, June 30, 2007
Tuning Database Buffer Cache
Introduction
Buffer pool or buffer cache is a memory structure within Oracle SGA that is used for caching data blocks in the memory. Accessing data from the memory is significantly faster than accessing data from disk. The goal of block buffer tuning is to efficiently cache frequently used data blocks in the buffer cache (SGA) and provide faster access to data.
Tuning block buffer is a key task in any Oracle tuning initiative and is a part of an ongoing tuning and monitoring of production databases. The task of database performance optimization by allocating appropriate memory structure within SGA is a very interesting and challenging task for a DBA. Generally, it takes a few iterations before you can reach to a satisfactory memory allocation. This article presents a tuning approach that greatly simplifies the tuning effort. The methodology discussed here will help DBAs in creating a proactive database tuning and monitoring strategy for their production databases. If you are a DBA responsible for tuning a database without enough physical memory on the server, you will find this article very informative and useful. With the help of an established methodology and tuning strategy, you can easily identify the problem areas and tune the SGA memory structure.
Step 1: Identify the amount of memory that you can allocate to block buffers. Create buffer pool without inducing significant paging or swapping.
Deciding the size of block buffer is critical to the overall system performance. A large block buffer will provide more memory space for caching data blocks in the SGA. In such a case, most SQL statements will find data blocks in the memory and a less time consuming IO will be performed by the database. The more the data cached in the memory, the faster is the database response. Life of DBAs would have been much simpler if we could cache the entire database (or even 50% of the database) in the block buffers, but we are limited by the physical memory available on the server. If we allocate a very large block buffer, the server may swap / page the SGA into a swap space on the disk (If SGA in not locked in the physical memory). In such circumstances, data will no longer be accessed from the memory, and CPU will have to work harder to manage the virtual memory of the server, thus degrading the overall system performance. A very large block buffer may defeat the purpose of caching data in memory for faster access, if the operating system is swapping / paging SGA pages. Within the limitations of the physical memory, the DBA should allocate block buffers to cache data in the SGA without inducing paging or swapping on the server. Hence, understanding the amount of memory available for block buffers is key to tuning the block buffers.
You can set the buffer cache by specifying DB_CACHE_SIZE (or DB_BLOCK_BUFFERS) initialization parameter as:-
DB_CACHE_SIZE = 128 M
Step 2: Identify how frequently data blocks are accessed from the buffer cache (a. k. a Block Buffer Hit Ratio).
Once you have allocated the available memory to block buffers, the next step is to identify the efficiency of block buffer usage. Oracle database maintains dynamic performance view V$BUFFER_POOL_STATISTICS with overall buffer usage statistics. This view maintains the following counts every time a data block is accessed either from the block buffers or from the disk:
NAME – Name of the buffer pool
PHYSICAL_READS – Number of physical reads
DB_BLOCK_GETS – Number of reads for INSERT, UPDATE and DELETE
CONSISTENT_GETS – Number of reads for SELECT
DB_BLOCK_GETS + CONSISTENT_GETS = Total Number of reads
Based on above statistics we can calculate the percentage of data blocks being accessed from the memory to that of the disk (block buffer hit ratio). The following SQL statement will return the block buffer hit ratio:
SELECT NAME, 100 – round ((PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS))*100,2) HitRatio
FROM V$BUFFER_POOL_STATISTICS;
Before measuring the database buffer hit ratio, it is very important to check that the database is running in a steady state with normal workload and no unusual activity has taken place. Buffer hit ratio can be skewed depending on the type of SQL statements running in the database since startup. For example, when you run a SQL statement just after database startup, no data blocks have been cached in the block buffers. At this point, Oracle reads the data blocks from the disk and will cache the blocks in the memory. If you run the same SQL statement again, then most likely the data blocks will still be present in the cache, and Oracle will not have to perform disk IO. If you run the same SQL statement multiple times you will get a higher buffer hit ratio. On the other hand, if you either run SQL statements that rarely query the same data, or run a select on a very large table, the data block may not be in the buffer cache and Oracle will have to perform disk IO, thereby lowering the buffer hit ratio.
A hit ratio of 95% or greater is considered to be a good hit ratio for OLTP systems. The hit ratio for DSS (Decision Support System) may vary depending on the database load. A lower hit ratio means Oracle is performing more disk IO on the server. In such a situation, you can increase the size of database block buffers to increase the database performance. You may have to increase the physical memory on the server if the server starts swapping after increasing block buffers.
Step 3: Identify frequently used and rarely used data blocks. Cache frequently used blocks and discard rarely used blocks.
If you have a low buffer hit ratio and you cannot increase the size of the database block buffers, you can still gain some performance advantage by tuning the block buffers and efficiently caching the data block that will provide maximum benefits. Ideally, we should cache data blocks that are either frequently used in SQL statements, or data blocks used by performance sensitive SQL statements (A SQL statement whose performance is critical to the system performance). An ad-hoc query that scans a large table can significantly degrade overall database performance. A SQL on a large table may flush out frequently used data blocks from the buffer cache to store data blocks from the large table. During the peak time, ad-hoc queries that select data from large tables or from tables that are rarely used should be avoided. If we cannot avoid such queries, we can limit the impact on the buffer cache by using RECYCLE buffer pool.
A DBA can create multiple buffer pools in the SGA to store data blocks efficiently. For example, we can use RECYCLE pool to cache data blocks that are rarely used in the application. Typically, this will be a small area in the SGA to store data blocks for current SQL statement / transaction that we do not intend to hold in the memory after the transaction is completed. Similarly, we can use KEEP pool to cache data blocks that are frequently used by the application. Typically, this will be big enough to store data blocks that we want to always keep in memory. By storing data blocks in KEEP and RECYCLE pools you can store frequently used data blocks separately from the rarely used data blocks, and control which data blocks are flushed from the buffer cache. Using RECYCLE pool, we can also prevent a large table scan from flushing frequently used data blocks. You can create the RECYCLE and KEEP pools by specifying the following init.ora parameters:
DB_KEEP_CACHE_SIZE =
DB_RECYCLE_CACHE_SIZE = < size of RECYCLE pool>
When you use the above parameters, the total memory allocated to the block buffers is the sum of DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, and DB_CACHE_SIZE.
Step 4: Assign tables to KEEP / RECYCLE pool. Identify buffer hit ratio for KEEP, RECYCLE, and DEFAULT pool. Adjust the initialization parameters for optimum performance.
By default, data blocks are cached in the DEFAULT pool. The DBA must configure the table to use the KEEP or the RECYCLE pool by specifying BUFFER_POOL keyword in the CREATE TABLE or the ALTER TABLE statement. For example, you can assign a table to the recycle pool by using the following ALTER TABLE SQL statement.
ALTER TABLE TABLE_NAME STORAGE (BUFFER_POOL RECYCLE)
The DBA can take help from application designers in identifying tables that should use KEEP or RECYCLE pool. You can also query X$BH to examine the current block buffer usage by database objects (You must log in as SYS to query X$BH). The following query returns a list of tables that are rarely used and can be assigned to the RECYCLE pool.
Col object_name format a30
Col object_type format a20
SELECT o.owner, object_name, object_type, COUNT(1) buffers
FROM SYS.x$bh, dba_objects o
WHERE (tch = 1 OR (tch = 0 AND lru_flag < 8))
AND obj = o. object_id
AND o.owner not in ('SYSTEM','SYS')
GROUP BY o.owner, object_name, object_type
ORDER BY buffers;
The following query will return a list of tables that are frequently used by SQL statements and can be assigned to the KEEP pool.
SELECT o.owner, object_name, object_type, COUNT(1) buffers
FROM SYS.x$bh, dba_objects o
WHERE tch > 10
AND lru_flag = 8
AND obj = o.object_id
AND o.owner not in ('SYSTEM','SYS')
GROUP BY o.owner, object_name, object_type
ORDER BY buffers;
Once you have setup the database to use KEEP and RECYCLE pools, you can monitor the buffer hit ratio by querying V$BUFFER_POOL_STATISTICS and V$DB_CACHE_ADVICE to adjust the buffer pool initialization parameters.
Step 5: Identify the amount of memory needed to maintain required performance.
Oracle 9i maintains block buffer advisory information in V$DB_CACHE_ADVICE. This view contains simulated physical reads for a range of buffer cache sizes. The DBA can query this view to estimate buffer cache requirement for the database. The cache advisory can be activated by setting DB_CACHE_ADIVE initialization parameter.
DB_CACHE_ADVICE = ON
There is a minor overhead associated with cache advisory collection. Hence, it is not advisable to collect these statistics in production databases until there is a need to tune the buffer cache. The DBA can turn on DB_CACHE_ADVISE dynamically for the duration of sample workload period and collect advisory statistics.
Conclusion
Using this methodical approach, a DBA can easily identify the problem areas, and tune the database block buffers. The DBA can create the following buffer pool to efficiently cache data blocks in SGA:
KEEP: Cache tables that are very critical for system performance. Typically, lookup tables are very good candidates for the KEEP pool. The DBA should create the KEEP pool large enough to maintain 99% buffer hit ratio on this pool.
RECYCLE: Cache tables that are not critical for system performance. Typically, a table containing historical information that is either rarely queried or used by batch process is a good candidate for the RECYCLE pool. The DBA should create the RECYCLE pool large enough to finish the current transaction.
DEFAULT: Cache tables that do not belong to either KEEP or RECYCLE pool.
The DBA can setup OEM jobs, Oracle statspack, or custom monitoring scripts to monitor your production database block buffer efficiency, and to identify and tune the problem area.
Buffer pool or buffer cache is a memory structure within Oracle SGA that is used for caching data blocks in the memory. Accessing data from the memory is significantly faster than accessing data from disk. The goal of block buffer tuning is to efficiently cache frequently used data blocks in the buffer cache (SGA) and provide faster access to data.
Tuning block buffer is a key task in any Oracle tuning initiative and is a part of an ongoing tuning and monitoring of production databases. The task of database performance optimization by allocating appropriate memory structure within SGA is a very interesting and challenging task for a DBA. Generally, it takes a few iterations before you can reach to a satisfactory memory allocation. This article presents a tuning approach that greatly simplifies the tuning effort. The methodology discussed here will help DBAs in creating a proactive database tuning and monitoring strategy for their production databases. If you are a DBA responsible for tuning a database without enough physical memory on the server, you will find this article very informative and useful. With the help of an established methodology and tuning strategy, you can easily identify the problem areas and tune the SGA memory structure.
Step 1: Identify the amount of memory that you can allocate to block buffers. Create buffer pool without inducing significant paging or swapping.
Deciding the size of block buffer is critical to the overall system performance. A large block buffer will provide more memory space for caching data blocks in the SGA. In such a case, most SQL statements will find data blocks in the memory and a less time consuming IO will be performed by the database. The more the data cached in the memory, the faster is the database response. Life of DBAs would have been much simpler if we could cache the entire database (or even 50% of the database) in the block buffers, but we are limited by the physical memory available on the server. If we allocate a very large block buffer, the server may swap / page the SGA into a swap space on the disk (If SGA in not locked in the physical memory). In such circumstances, data will no longer be accessed from the memory, and CPU will have to work harder to manage the virtual memory of the server, thus degrading the overall system performance. A very large block buffer may defeat the purpose of caching data in memory for faster access, if the operating system is swapping / paging SGA pages. Within the limitations of the physical memory, the DBA should allocate block buffers to cache data in the SGA without inducing paging or swapping on the server. Hence, understanding the amount of memory available for block buffers is key to tuning the block buffers.
You can set the buffer cache by specifying DB_CACHE_SIZE (or DB_BLOCK_BUFFERS) initialization parameter as:-
DB_CACHE_SIZE = 128 M
Step 2: Identify how frequently data blocks are accessed from the buffer cache (a. k. a Block Buffer Hit Ratio).
Once you have allocated the available memory to block buffers, the next step is to identify the efficiency of block buffer usage. Oracle database maintains dynamic performance view V$BUFFER_POOL_STATISTICS with overall buffer usage statistics. This view maintains the following counts every time a data block is accessed either from the block buffers or from the disk:
NAME – Name of the buffer pool
PHYSICAL_READS – Number of physical reads
DB_BLOCK_GETS – Number of reads for INSERT, UPDATE and DELETE
CONSISTENT_GETS – Number of reads for SELECT
DB_BLOCK_GETS + CONSISTENT_GETS = Total Number of reads
Based on above statistics we can calculate the percentage of data blocks being accessed from the memory to that of the disk (block buffer hit ratio). The following SQL statement will return the block buffer hit ratio:
SELECT NAME, 100 – round ((PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS))*100,2) HitRatio
FROM V$BUFFER_POOL_STATISTICS;
Before measuring the database buffer hit ratio, it is very important to check that the database is running in a steady state with normal workload and no unusual activity has taken place. Buffer hit ratio can be skewed depending on the type of SQL statements running in the database since startup. For example, when you run a SQL statement just after database startup, no data blocks have been cached in the block buffers. At this point, Oracle reads the data blocks from the disk and will cache the blocks in the memory. If you run the same SQL statement again, then most likely the data blocks will still be present in the cache, and Oracle will not have to perform disk IO. If you run the same SQL statement multiple times you will get a higher buffer hit ratio. On the other hand, if you either run SQL statements that rarely query the same data, or run a select on a very large table, the data block may not be in the buffer cache and Oracle will have to perform disk IO, thereby lowering the buffer hit ratio.
A hit ratio of 95% or greater is considered to be a good hit ratio for OLTP systems. The hit ratio for DSS (Decision Support System) may vary depending on the database load. A lower hit ratio means Oracle is performing more disk IO on the server. In such a situation, you can increase the size of database block buffers to increase the database performance. You may have to increase the physical memory on the server if the server starts swapping after increasing block buffers.
Step 3: Identify frequently used and rarely used data blocks. Cache frequently used blocks and discard rarely used blocks.
If you have a low buffer hit ratio and you cannot increase the size of the database block buffers, you can still gain some performance advantage by tuning the block buffers and efficiently caching the data block that will provide maximum benefits. Ideally, we should cache data blocks that are either frequently used in SQL statements, or data blocks used by performance sensitive SQL statements (A SQL statement whose performance is critical to the system performance). An ad-hoc query that scans a large table can significantly degrade overall database performance. A SQL on a large table may flush out frequently used data blocks from the buffer cache to store data blocks from the large table. During the peak time, ad-hoc queries that select data from large tables or from tables that are rarely used should be avoided. If we cannot avoid such queries, we can limit the impact on the buffer cache by using RECYCLE buffer pool.
A DBA can create multiple buffer pools in the SGA to store data blocks efficiently. For example, we can use RECYCLE pool to cache data blocks that are rarely used in the application. Typically, this will be a small area in the SGA to store data blocks for current SQL statement / transaction that we do not intend to hold in the memory after the transaction is completed. Similarly, we can use KEEP pool to cache data blocks that are frequently used by the application. Typically, this will be big enough to store data blocks that we want to always keep in memory. By storing data blocks in KEEP and RECYCLE pools you can store frequently used data blocks separately from the rarely used data blocks, and control which data blocks are flushed from the buffer cache. Using RECYCLE pool, we can also prevent a large table scan from flushing frequently used data blocks. You can create the RECYCLE and KEEP pools by specifying the following init.ora parameters:
DB_KEEP_CACHE_SIZE =
DB_RECYCLE_CACHE_SIZE = < size of RECYCLE pool>
When you use the above parameters, the total memory allocated to the block buffers is the sum of DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, and DB_CACHE_SIZE.
Step 4: Assign tables to KEEP / RECYCLE pool. Identify buffer hit ratio for KEEP, RECYCLE, and DEFAULT pool. Adjust the initialization parameters for optimum performance.
By default, data blocks are cached in the DEFAULT pool. The DBA must configure the table to use the KEEP or the RECYCLE pool by specifying BUFFER_POOL keyword in the CREATE TABLE or the ALTER TABLE statement. For example, you can assign a table to the recycle pool by using the following ALTER TABLE SQL statement.
ALTER TABLE TABLE_NAME STORAGE (BUFFER_POOL RECYCLE)
The DBA can take help from application designers in identifying tables that should use KEEP or RECYCLE pool. You can also query X$BH to examine the current block buffer usage by database objects (You must log in as SYS to query X$BH). The following query returns a list of tables that are rarely used and can be assigned to the RECYCLE pool.
Col object_name format a30
Col object_type format a20
SELECT o.owner, object_name, object_type, COUNT(1) buffers
FROM SYS.x$bh, dba_objects o
WHERE (tch = 1 OR (tch = 0 AND lru_flag < 8))
AND obj = o. object_id
AND o.owner not in ('SYSTEM','SYS')
GROUP BY o.owner, object_name, object_type
ORDER BY buffers;
The following query will return a list of tables that are frequently used by SQL statements and can be assigned to the KEEP pool.
SELECT o.owner, object_name, object_type, COUNT(1) buffers
FROM SYS.x$bh, dba_objects o
WHERE tch > 10
AND lru_flag = 8
AND obj = o.object_id
AND o.owner not in ('SYSTEM','SYS')
GROUP BY o.owner, object_name, object_type
ORDER BY buffers;
Once you have setup the database to use KEEP and RECYCLE pools, you can monitor the buffer hit ratio by querying V$BUFFER_POOL_STATISTICS and V$DB_CACHE_ADVICE to adjust the buffer pool initialization parameters.
Step 5: Identify the amount of memory needed to maintain required performance.
Oracle 9i maintains block buffer advisory information in V$DB_CACHE_ADVICE. This view contains simulated physical reads for a range of buffer cache sizes. The DBA can query this view to estimate buffer cache requirement for the database. The cache advisory can be activated by setting DB_CACHE_ADIVE initialization parameter.
DB_CACHE_ADVICE = ON
There is a minor overhead associated with cache advisory collection. Hence, it is not advisable to collect these statistics in production databases until there is a need to tune the buffer cache. The DBA can turn on DB_CACHE_ADVISE dynamically for the duration of sample workload period and collect advisory statistics.
Conclusion
Using this methodical approach, a DBA can easily identify the problem areas, and tune the database block buffers. The DBA can create the following buffer pool to efficiently cache data blocks in SGA:
KEEP: Cache tables that are very critical for system performance. Typically, lookup tables are very good candidates for the KEEP pool. The DBA should create the KEEP pool large enough to maintain 99% buffer hit ratio on this pool.
RECYCLE: Cache tables that are not critical for system performance. Typically, a table containing historical information that is either rarely queried or used by batch process is a good candidate for the RECYCLE pool. The DBA should create the RECYCLE pool large enough to finish the current transaction.
DEFAULT: Cache tables that do not belong to either KEEP or RECYCLE pool.
The DBA can setup OEM jobs, Oracle statspack, or custom monitoring scripts to monitor your production database block buffer efficiency, and to identify and tune the problem area.
Friday, June 29, 2007
Sql Statement We Use to Generate Tablespace Usage Report
A Sql Statement We Use to Generate Tablespace Usage Report Taking Autoextend Into Consideration
This article describes the query we created in order to generate Database level Tablespace usage in percentage with Autoextend on.
In past we used a query which was showing total assigned space to a particular tablespace and was not including future growth when Autoextend On.
So it was giving wrong numbers:
:-> Example: Size of tablespace is 1GB – Used Space is 900MB – Free Space is 100MB –
Now it has Autoextend On with 4GB Total extension capabilities.
It was sending us Paging telling only 10 Percentage free –instead it should take Total 4GB in consideration and by that it has total 3.1GB Free. Soo we dug into the data dictionary and found base sys views/tables from where we can get that information and refined our query with the same.
Query:
REM Following Sql statement will generate tablespace usage report taking autoextend into consideration
sqlplus "/ as sysdba"
set linesize 250
set pagesize 70
col tablespace_name format a20
select a.tablespace_name, SUM(a.bytes)/1024/1024 "CurMb",
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "MaxMb",
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "TotalUsed",
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "TotalFree",
round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
from dba_data_files a,
sys.filext$ b,
(SELECT c.tablespace_name , sum(c.bytes) "Free" FROM DBA_FREE_SPACE c GROUP BY TABLESPACE_NAME) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024
/
exit;
This article describes the query we created in order to generate Database level Tablespace usage in percentage with Autoextend on.
In past we used a query which was showing total assigned space to a particular tablespace and was not including future growth when Autoextend On.
So it was giving wrong numbers:
:-> Example: Size of tablespace is 1GB – Used Space is 900MB – Free Space is 100MB –
Now it has Autoextend On with 4GB Total extension capabilities.
It was sending us Paging telling only 10 Percentage free –instead it should take Total 4GB in consideration and by that it has total 3.1GB Free. Soo we dug into the data dictionary and found base sys views/tables from where we can get that information and refined our query with the same.
Query:
REM Following Sql statement will generate tablespace usage report taking autoextend into consideration
sqlplus "/ as sysdba"
set linesize 250
set pagesize 70
col tablespace_name format a20
select a.tablespace_name, SUM(a.bytes)/1024/1024 "CurMb",
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "MaxMb",
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "TotalUsed",
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "TotalFree",
round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
from dba_data_files a,
sys.filext$ b,
(SELECT c.tablespace_name , sum(c.bytes) "Free" FROM DBA_FREE_SPACE c GROUP BY TABLESPACE_NAME) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024
/
exit;
An Approach to Tuning a SQL Statement
While application knowledge and experience is helpful, We've got a methodical approach to use when asked to tune a SQL statement.
Here's the basic approach/process that can be used to diagnose and analyze a SQL statement..
Analyze the Statement
1. We can start by reviewing the WHERE clause, dividing it into clauses that restrict rows and those that join tables.
2. We can locate candidate Driving Tables by looking at the "restrict rows" clauses as determining which table has the most restrictive clauses ( i.e. those that will result in the fewest rows). If not sure between a couple of candidates, we can pick one.
3. Next we have to list all the tables in the FROM clause, starting with the driving table, using the JOIN criteria to link from one table to the next. This generates an ordered list of how the database should be accessing the tables, starting with the candidate driving table. This exercise generates all possible ways to join the tables together as well as making sure there are Join criteria for all the tables in the FROM clause.
4. Look for indexes on the driving table and determine which should be used or if a full table scan would be best. If we find a better index than the one the optimizer picks then we add an Index hint to get the desired index to be used. If a full table scan is in order, we consider adding a parallel hint.
5. We can try to join two tables that have restrictive where clauses before joining two tables that don't have any such clauses.
6. We now have one or more candidate explain plan(s) that need to be tested.
Compare Plans
1. Next we compare the actual explain plan of the statement in question with the candidate plans generated in the above exercise
2. Looking at the actual data (and running some queries to collect metrics) we consider trying different driving tables and join orders.
3. We review and compare each, and subjectively arrive at a best guess plan.
Test it out
1. The most effective hint we've found is the ORDERED hint. This works especially well when you have a lot of tables in the FROM clause.
2. We add /*+ ordered */ after the Select verb and the list the tables in the from clause from first to last the way we listed them in Analysis Step 3. Adding other hints like /*+ Full (driving table alias) */ is also a good way to get the optimizer to use the proper driving table.
3. Run an explain plan using the above HINTs.
4. If the plan looks good, execute it for real. Set Timing On and set Autotrace On (hopefully you have that enabled in your database)
5. Run the old and new statements several times to see how the timings work out. The Autotrace summary of blocks touched is a. good indicator of performance: the fewer blocks touched, the better.
Tuning Hints and Tips
1. There are many other hints and techniques that can influence an explain plan. Keep in mind that a Hash Join is nearly always better than a Merge Join
2. Nested Loop is better only if a few rows are being retrieved.
3. Tuning sub-selects can be tricky. We will often rewrite a statement to replace " IN (Select...) or a NOT IN" with a join to an in-line view and achieve significant performance increase by avoiding executing thousands of Nested Loop index reads with a hash join. Listing more statement rewrite options is beyond the scope of this article.
Those are the basic steps We follow. Give them a try and over time we are confident your SQL tuning skills will expand.
Here's the basic approach/process that can be used to diagnose and analyze a SQL statement..
Analyze the Statement
1. We can start by reviewing the WHERE clause, dividing it into clauses that restrict rows and those that join tables.
2. We can locate candidate Driving Tables by looking at the "restrict rows" clauses as determining which table has the most restrictive clauses ( i.e. those that will result in the fewest rows). If not sure between a couple of candidates, we can pick one.
3. Next we have to list all the tables in the FROM clause, starting with the driving table, using the JOIN criteria to link from one table to the next. This generates an ordered list of how the database should be accessing the tables, starting with the candidate driving table. This exercise generates all possible ways to join the tables together as well as making sure there are Join criteria for all the tables in the FROM clause.
4. Look for indexes on the driving table and determine which should be used or if a full table scan would be best. If we find a better index than the one the optimizer picks then we add an Index hint to get the desired index to be used. If a full table scan is in order, we consider adding a parallel hint.
5. We can try to join two tables that have restrictive where clauses before joining two tables that don't have any such clauses.
6. We now have one or more candidate explain plan(s) that need to be tested.
Compare Plans
1. Next we compare the actual explain plan of the statement in question with the candidate plans generated in the above exercise
2. Looking at the actual data (and running some queries to collect metrics) we consider trying different driving tables and join orders.
3. We review and compare each, and subjectively arrive at a best guess plan.
Test it out
1. The most effective hint we've found is the ORDERED hint. This works especially well when you have a lot of tables in the FROM clause.
2. We add /*+ ordered */ after the Select verb and the list the tables in the from clause from first to last the way we listed them in Analysis Step 3. Adding other hints like /*+ Full (driving table alias) */ is also a good way to get the optimizer to use the proper driving table.
3. Run an explain plan using the above HINTs.
4. If the plan looks good, execute it for real. Set Timing On and set Autotrace On (hopefully you have that enabled in your database)
5. Run the old and new statements several times to see how the timings work out. The Autotrace summary of blocks touched is a. good indicator of performance: the fewer blocks touched, the better.
Tuning Hints and Tips
1. There are many other hints and techniques that can influence an explain plan. Keep in mind that a Hash Join is nearly always better than a Merge Join
2. Nested Loop is better only if a few rows are being retrieved.
3. Tuning sub-selects can be tricky. We will often rewrite a statement to replace " IN (Select...) or a NOT IN" with a join to an in-line view and achieve significant performance increase by avoiding executing thousands of Nested Loop index reads with a hash join. Listing more statement rewrite options is beyond the scope of this article.
Those are the basic steps We follow. Give them a try and over time we are confident your SQL tuning skills will expand.
Manually un-installed Oracle 10g
How we Manually un-installed Oracle 10g - Windows
As a testing box for RMAN and Flashback query, we are supposed to install and perform the required tests on 10g DB. During the first iteration we encountered that OUI was not responding, I believed it to be the security settings on the Windows box. But it was not getting better after changing the security settings on Windows.
Here the steps we took to manually un-install Oracle 10g on Windows 2003 / XP.
On the desktop, we Right Click on MyComputer Icon and selected "Manage". On the left hand pane, we selected "Service and Applications" from Computer Management.
** Alternatively you can also select Services from Control Panel.
In the next step we Shutdown Oracle Instance and related Oracle Services. Some of them might give errors " Unable to shutdown". It could be because of improper installation. Now after shutting down the Oracle services, we had to locate the registry entries for Oracle and delete them manually.
We made a backup of registry.
We then clicked on Start --> Run --> Regedit.
Looked for the following entries:
=============================================================================
HKEY_CURRENT_USER\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\Oracle.oracle
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleDBConsole
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle10g_home
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OraclService
=========================================================================
Deleting the above entries from the registry required a reboot, so we did a Reboot of the windows box.
Some entries in the registry like registering of some components required cleaning software. We did it using "CleanMyPCRegistry”. This removed any discrepancies in the registry.
Now in the next step we deleted the physical directories.
C:\Oracle
Deleted the Oracle Program Files directory:
C:\Program Files\Oracle
Deleted the Oracle Start Menu shortcuts directory:
C:\Documents and Settings\All Users\Start Menu\Programs\Oracle
Removed Oracle references from the path. To edit the path we went to:
Start->Settings->Control Panel->System->Advanced->Environment Variables
We edited both of the environment variables user PATH and system PATH. Remove any Oracle references in them.
Then we removed Oracle.DataAccess and any Polic.Oracle files from the GAC which is at:
C:\Windows\assembly\
We also received an error while removing the $ORACLE_HOME/bin directory. While un-installing oci.dll (Oracle Call interface could not be deleted.
To resolve this, we renamed the Oracle/bin folder and re-boot the machine. After reboot, we are able to delete the oci.dll. We then proceeded with the proper installation of Oracle 10g R2.
As a testing box for RMAN and Flashback query, we are supposed to install and perform the required tests on 10g DB. During the first iteration we encountered that OUI was not responding, I believed it to be the security settings on the Windows box. But it was not getting better after changing the security settings on Windows.
Here the steps we took to manually un-install Oracle 10g on Windows 2003 / XP.
On the desktop, we Right Click on MyComputer Icon and selected "Manage". On the left hand pane, we selected "Service and Applications" from Computer Management.
** Alternatively you can also select Services from Control Panel.
In the next step we Shutdown Oracle Instance and related Oracle Services. Some of them might give errors " Unable to shutdown". It could be because of improper installation. Now after shutting down the Oracle services, we had to locate the registry entries for Oracle and delete them manually.
We made a backup of registry.
We then clicked on Start --> Run --> Regedit.
Looked for the following entries:
=============================================================================
HKEY_CURRENT_USER\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\Oracle.oracle
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleDBConsole
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle10g_home
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OraclService
=========================================================================
Deleting the above entries from the registry required a reboot, so we did a Reboot of the windows box.
Some entries in the registry like registering of some components required cleaning software. We did it using "CleanMyPCRegistry”. This removed any discrepancies in the registry.
Now in the next step we deleted the physical directories.
C:\Oracle
Deleted the Oracle Program Files directory:
C:\Program Files\Oracle
Deleted the Oracle Start Menu shortcuts directory:
C:\Documents and Settings\All Users\Start Menu\Programs\Oracle
Removed Oracle references from the path. To edit the path we went to:
Start->Settings->Control Panel->System->Advanced->Environment Variables
We edited both of the environment variables user PATH and system PATH. Remove any Oracle references in them.
Then we removed Oracle.DataAccess and any Polic.Oracle files from the GAC which is at:
C:\Windows\assembly\
We also received an error while removing the $ORACLE_HOME/bin directory. While un-installing oci.dll (Oracle Call interface could not be deleted.
To resolve this, we renamed the Oracle/bin folder and re-boot the machine. After reboot, we are able to delete the oci.dll. We then proceeded with the proper installation of Oracle 10g R2.
Subscribe to:
Posts (Atom)
