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.
Subscribe to:
Posts (Atom)
