Dear Friends,
The following errors were generated continuously in the test database alert log file.
1). ORA-25153: Temporary Tablespace is Empty
2). GATHER_STATS_JOB encountered errors. Check the trace file.
3). ORA-20000: Unable to analyze TABLE "SIRSI"."HEADING4", insufficient privileges or does not exist.
But when we query to see the information about temporary tablespace and tempfiles in the database, the following findings were observed.
## To check TEMP tablespace and tempfile details.
SQL> SELECT file_name, bytes FROM dba_temp_files;
no rows selected
SQL> SELECT * FROM v$tempfile;
no rows selected
SQL> SELECT * FROM v$tablespace WHERE name='TEMP';
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
3 TEMP NO NO YES
SQL> SELECT tablespace_name, contents FROM dba_tablespaces WHERE tablespace_name='TEMP';
TABLESPACE_NAME CONTENTS
------------------------------ ---------
TEMP TEMPORARY
SQL> SELECT DISTINCT temporary_tablespace FROM dba_users;
TEMPORARY_TABLESPACE
------------------------------
TEMP
## Gather Schema Statistics.
In Oralce 10g, when the data of a table is grown to certain threshold value i.e. 10% of data, Automatically Gather Schema Statistics is performed on those tables.And also it needs enough free space in the temporary tablespace for sorting. Due to this reason above messages generated in the alert log file.
Cause: The TEST Database has recently been cloned from PRODuction Database. So, we realized that the step, adding a tempfile to the temp tablespace, was missed mistakenly. In fact the tempfile was copied and it is physically existing in the server, but not added to the TEMP tablespace as part of the cloning.
Solution:
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/10.2.0/oradata/test/temp01.dbf' REUSE;
After adding a tempfile to the TEMP tablespace, no errors generated regarding Gather Schema Statistics and temp tablespace afterward !!!.
Note: if you are receiving an error "ORA-20000: Unable to analyze TABLE "SIRSI"."HEADING4", insufficient privileges or does not exist." due to other reasons. Please take a look at the Oracle Metalink Note ID: 333966.1
This article, written by me, is also available in Oracle Metalink. Note ID: 427974.1
Best Regards,
Sabdar Syed.
Tuesday, March 13, 2007
Tuesday, February 27, 2007
Installation of Oracle 10g Release 2 (10.2.0.1.0) on Fedora Core Linux 6
Dear friends,
After fixing some general errors during the process of installation, I could successfully install and configure Oracle 10gR2 (10.2.0.1) on Fedora Core Linux 6. I would like to share the procedure (step-by-step) of Installation what I followed. Note: In fact Oracle 10gR2 is not supported on Fedora Core 6
Operating System Details:
Linux testserver 2.6.18-1.2798.fc6 #1 SMP Mon Oct 16 14:54:20 EDT 2006 i686 i686 i386 GNU/Linux
Database Verison Details:
Oracle 10g Release 2 (10.2.0.1.0)
Download Software: (10201_database_linux32.zip) http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10201linuxsoft.html
Pre-Installation tasks:
Creating DBA group and ORACLE user account in Linux
Login to the System as ROOT and execute the below commands.
$ su – root
Password:
# groupadd dba
# useradd –d "/home/oracle" –m –g dba oracle
# passwd oracle
Configuring Kernel Parameters
Check and edit the /etc/sysctl.conf file with the following lines.
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144
Note: Make sure to reboot the system or run the command " /sbin/sysctl –p" to change the kernel parameters.
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
Make sure that " SELINUX=disabled" in the the /etc/selinux/config file
Edit the /etc/redhat-release file replacing the current release information (Fedora Core release 6 (Zod)) with redhat-4
Creating an Oracle Base Directory
# mkdir –p /u01/app/oracle
# mkdir –p /u01/app/oradata
# mkdir –p /u02/app/oradata
# chown –R oracle:dba /u01/app/oracle
# chown –R oracle:dba /u0 1/app/oradata /u02/app/oradata
# chmod –R 775 /u01/app/oracle
# chmod –R 775 /u0 1/app/oradata /u02/app/oradata
Setting Oracle Environment
Login as Oracle Unix user and change the .bash_profile with the following variables.
Su – oracle
Password:
ORACLE_HOME=/u01/app/oracle
ORACLE_SID=mydb
LD_LIBRARY_PATH=$ORACLE_HOME/lib
TNS_ADMIN=$ORACLE_HOME/network
PATH=$PATH:$ORACLE_HOME/bin
Export ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH TNS_ADMIN
Save the bash profile after changing: wq!
Insallation
Login as oracle and unzip the Oracle 10g software
Su – oracle
Password:
$ unzip 10201_database_linux32.zip
Check all pre-req packages are installed, if not install them
To check the packages information: # rpm –q
To install the packages: # rpm –q
Require packages for installation:
rpm -q binutils gcc glibc glibc-headers glibc-kernheaders glibc-devel compat-libstdc++ cpp compat-gcc make compat-db compat-gcc-c++ compat-libstdc++ compat-libstdc++-devel openmotif openmotif21 setarch pdksh libaio libaio-devel
Important: For successful installation, the rpms "libXp-1.0.0-8.i386.rpm and libaio-0.3.106-3.2.i386.rpm" must have installed. Download these from : http://download.fedora.redhat.com/pub/fedora/linux/core/6/i386/os/Fedora/RPMS
Go to database directory and launch the installer.
$./runInstaller
Provide the required information asked by wizards during installation.
Post – Installation tasks:
Edit the /etc/redhat-release file restoring the original release information:
Fedora Core release 6 (Zod)
I didn't let DBCA to create the default database during the installation; I selected Install Software Only option. After the successful installation of Oracle 10g Software, I created the database manually with my requiremtns. The following are the particulars.
Login as oracel
Make sure that all the required environment variables are Set i.e. ORACLE_HOME and ORACLE_SID etc..
Prepare the initialization parameter with required values
Connect to SQL*Plus as sysdba
$ sqlplus /nolog
SQL> connect /as sysdba
Create the database with the following script.
SQL> startup nomount pfile=/initmydb.ora;
SQL> @$HOME/create_db_manually.sql
create_db_manually.sql file contains
CREATE DATABASE unic
USER SYS IDENTIFIED BY sys
USER SYSTEM IDENTIFIED BY manager
LOGFILE GROUP 1 ('/u01/app/oradata/unic/redo01.log') SIZE 50M,
GROUP 2 ('/u01/app/oradata/unic/redo02.log') SIZE 50M,
GROUP 3 ('/u01/app/oradata/unic/redo03.log') SIZE 50M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u02/app/oradata/unic/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u02/app/oradata/unic/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
datafile '/u02/app/oradata/unic/users01.dbf'
SIZE 20M REUSE
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u02/app/oradata/unic/temp01.dbf'
SIZE 200M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/u02/app/oradata/unic/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE 2000m;
Run Scripts to Build Data Dictionary Views
CONNECT SYS/password AS SYSDBA
SQL>@$ORACLE_HOME \rdbms\admin\catalog.sql
SQL>@$ORACLE_HOME \rdbms\admin\catproc.sql
Errors generated during the installation
>>Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2Failed
Edit the /etc/redhat-release file replacing the current release information (Fedora Core release 6 (Zod)) with redhat-4
>> Exception java.lang.UnsatisfiedLinkError: /tmp/OraInstall2007-02-25_03-22-29PM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory occurred..java.lang.UnsatisfiedLinkError: /tmp/OraInstall2007-02-25_03-22-29PM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directoryat java.lang.ClassLoader$NativeLibrary.load(Native Method)at java.lang.ClassLoader.loadLibrary0(Unknown Source)
……
……
Download and install the libXp-1.0.0-8.i386.rpm
http://download.fedora.redhat.com/pub/fedora/linux/core/6/i386/os/Fedora/RPMS/libXp-1.0.0-8.i386.rpm
#rpm –ivh libXp-1.0.0-8.i386.rpm
>> error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
Download and install the libaio-0.3.106-3.2.i386.rpm
http://download.fedora.redhat.com/pub/fedora/linux/core/6/i386/os/Fedora/RPMS/libXp-1.0.0-8.i386.rpm
# rpm -ivh libaio-0.3.106-3.2.i386.rpm
References:
http://download-uk.oracle.com/docs/cd/B19306_01/install.102/b15660/toc.htm
http://www.puschitz.com/InstallingOracle10g.shtml
http://ivan.kartik.sk/oracle/install_ora10gR2_fedora.html
Regards,
Sabdar Syed.
After fixing some general errors during the process of installation, I could successfully install and configure Oracle 10gR2 (10.2.0.1) on Fedora Core Linux 6. I would like to share the procedure (step-by-step) of Installation what I followed. Note: In fact Oracle 10gR2 is not supported on Fedora Core 6
Operating System Details:
Linux testserver 2.6.18-1.2798.fc6 #1 SMP Mon Oct 16 14:54:20 EDT 2006 i686 i686 i386 GNU/Linux
Database Verison Details:
Oracle 10g Release 2 (10.2.0.1.0)
Download Software: (10201_database_linux32.zip) http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10201linuxsoft.html
Pre-Installation tasks:
Creating DBA group and ORACLE user account in Linux
Login to the System as ROOT and execute the below commands.
$ su – root
Password:
# groupadd dba
# useradd –d "/home/oracle" –m –g dba oracle
# passwd oracle
Configuring Kernel Parameters
Check and edit the /etc/sysctl.conf file with the following lines.
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144
Note: Make sure to reboot the system or run the command " /sbin/sysctl –p" to change the kernel parameters.
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
Make sure that " SELINUX=disabled" in the the /etc/selinux/config file
Edit the /etc/redhat-release file replacing the current release information (Fedora Core release 6 (Zod)) with redhat-4
Creating an Oracle Base Directory
# mkdir –p /u01/app/oracle
# mkdir –p /u01/app/oradata
# mkdir –p /u02/app/oradata
# chown –R oracle:dba /u01/app/oracle
# chown –R oracle:dba /u0 1/app/oradata /u02/app/oradata
# chmod –R 775 /u01/app/oracle
# chmod –R 775 /u0 1/app/oradata /u02/app/oradata
Setting Oracle Environment
Login as Oracle Unix user and change the .bash_profile with the following variables.
Su – oracle
Password:
ORACLE_HOME=/u01/app/oracle
ORACLE_SID=mydb
LD_LIBRARY_PATH=$ORACLE_HOME/lib
TNS_ADMIN=$ORACLE_HOME/network
PATH=$PATH:$ORACLE_HOME/bin
Export ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH TNS_ADMIN
Save the bash profile after changing: wq!
Insallation
Login as oracle and unzip the Oracle 10g software
Su – oracle
Password:
$ unzip 10201_database_linux32.zip
Check all pre-req packages are installed, if not install them
To check the packages information: # rpm –q
To install the packages: # rpm –q
Require packages for installation:
rpm -q binutils gcc glibc glibc-headers glibc-kernheaders glibc-devel compat-libstdc++ cpp compat-gcc make compat-db compat-gcc-c++ compat-libstdc++ compat-libstdc++-devel openmotif openmotif21 setarch pdksh libaio libaio-devel
Important: For successful installation, the rpms "libXp-1.0.0-8.i386.rpm and libaio-0.3.106-3.2.i386.rpm" must have installed. Download these from : http://download.fedora.redhat.com/pub/fedora/linux/core/6/i386/os/Fedora/RPMS
Go to database directory and launch the installer.
$./runInstaller
Provide the required information asked by wizards during installation.
Post – Installation tasks:
Edit the /etc/redhat-release file restoring the original release information:
Fedora Core release 6 (Zod)
I didn't let DBCA to create the default database during the installation; I selected Install Software Only option. After the successful installation of Oracle 10g Software, I created the database manually with my requiremtns. The following are the particulars.
Login as oracel
Make sure that all the required environment variables are Set i.e. ORACLE_HOME and ORACLE_SID etc..
Prepare the initialization parameter with required values
Connect to SQL*Plus as sysdba
$ sqlplus /nolog
SQL> connect /as sysdba
Create the database with the following script.
SQL> startup nomount pfile=
SQL> @$HOME/create_db_manually.sql
create_db_manually.sql file contains
CREATE DATABASE unic
USER SYS IDENTIFIED BY sys
USER SYSTEM IDENTIFIED BY manager
LOGFILE GROUP 1 ('/u01/app/oradata/unic/redo01.log') SIZE 50M,
GROUP 2 ('/u01/app/oradata/unic/redo02.log') SIZE 50M,
GROUP 3 ('/u01/app/oradata/unic/redo03.log') SIZE 50M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u02/app/oradata/unic/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u02/app/oradata/unic/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
datafile '/u02/app/oradata/unic/users01.dbf'
SIZE 20M REUSE
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u02/app/oradata/unic/temp01.dbf'
SIZE 200M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/u02/app/oradata/unic/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE 2000m;
Run Scripts to Build Data Dictionary Views
CONNECT SYS/password AS SYSDBA
SQL>@$ORACLE_HOME \rdbms\admin\catalog.sql
SQL>@$ORACLE_HOME \rdbms\admin\catproc.sql
Errors generated during the installation
>>Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2Failed
Edit the /etc/redhat-release file replacing the current release information (Fedora Core release 6 (Zod)) with redhat-4
>> Exception java.lang.UnsatisfiedLinkError: /tmp/OraInstall2007-02-25_03-22-29PM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory occurred..java.lang.UnsatisfiedLinkError: /tmp/OraInstall2007-02-25_03-22-29PM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directoryat java.lang.ClassLoader$NativeLibrary.load(Native Method)at java.lang.ClassLoader.loadLibrary0(Unknown Source)
……
……
Download and install the libXp-1.0.0-8.i386.rpm
http://download.fedora.redhat.com/pub/fedora/linux/core/6/i386/os/Fedora/RPMS/libXp-1.0.0-8.i386.rpm
#rpm –ivh libXp-1.0.0-8.i386.rpm
>> error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
Download and install the libaio-0.3.106-3.2.i386.rpm
http://download.fedora.redhat.com/pub/fedora/linux/core/6/i386/os/Fedora/RPMS/libXp-1.0.0-8.i386.rpm
# rpm -ivh libaio-0.3.106-3.2.i386.rpm
References:
http://download-uk.oracle.com/docs/cd/B19306_01/install.102/b15660/toc.htm
http://www.puschitz.com/InstallingOracle10g.shtml
http://ivan.kartik.sk/oracle/install_ora10gR2_fedora.html
Regards,
Sabdar Syed.
Tuesday, February 20, 2007
Upgrade Oracle Database from 9.2.0.1 to 9.2.0.7 on Soalris 10 (Sun Sparc)
As per the requirement I have upgrded one of our test database from the Version Oracle 9.2.0.1 to 9.2.0.7 on Solaris 10 (Sun Sparc)
Details:
Operating Version Details : SunOS libtest 5.10 Generic_118833-23 sun4u sparc SUNW,Sun-Fire
Oracle Version Details (Current) : Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
Oracle Instance Name : testdb
Patchset (to be applied on) : p4163445_9207_solaris64.zip (Downloaded from www.metalink.oracle.com)
Requirements for installing the patchset # 4163445
Oracle Universal Installer Version Requirements
The Oracle Universal Installer release must be 10.1.0.4 (This includes in the patchset).
System Requirements
* Operating System is Solaris 10 (But it can be Solaris 8 or 9)
* Oracle9i release 2 (9.2.0.1.0) or later
Preinstallation Tasks
Upgrade and SYS Schema
During an upgrade from release 9.2.0.1 or later, the catpatch.sql script can take a long time if there are statistics for the SYS schema. Delete the statistics on all of the objects in the SYS schema, and then recollect the statistics after normal database open, if necessary.
To drop and re-create the statistics.
SQL> EXECUTE DBMS_STATS.DELETE_SCHEMA_STATS('SYS');
SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
Download and Extract the Installation Software
To download and extract the patch set installation software:
Download and extract the p4163445_9207_solaris64.zip patch set installation archive to a directory " /oracle9i/9207_patch"
To unzip and extract the installation files:
$ unzip p4163445_9207_solaris64.zip
Set the ORACLE_HOME and ORACLE_SID Environment Variables
To set the ORACLE_HOME and ORACLE_SID environment variables:
Bourne, Bash, or Korn shell:
$ ORACLE_HOME=/oracle9i
$ ORACLE_SID=testdb
$ export ORACLE_HOME ORACLE_SID
C shell:
% setenv ORACLE_HOME /oracle9i
% setenv ORACLE_SID testdb
The oracle_home is the Oracle home directory where the Oracle9i installation that you want to upgrade is installed, and sid is the SID of the database that you want to upgrade.
Shut Down Oracle Databases
Shut down any existing Oracle database instances with normal or immediate priority.
SQL> SHUTDOWN IMMEDIATE;
Stop All Processes
Stop all listener and other processes running in the Oracle home directory where you want to install the patch set.
LSNRCTL> stop
Back Up the System
Oracle recommends that you create a backup of the Oracle9i installation before you install the patch set.
We took the backup of Oracle home and all the database files.
Installation Tasks
Installing the Patch Set Interactively
To install the patch set interactively:
* Log in as the Oracle software owner (typically oracle).
su - oracle
* If you are not installing the software on the local system, enter the following command to direct X applications to display on the local system:
Bourne, Bash, or Korn shell:
$ DISPLAY=libtest:0.0 ; export DISPLAY
C shell:
% setenv DISPLAY libtest:0.0
Here, local_host is the host name or IP address of the system that you want to use to display the Installer (your workstation or PC).
* Enter following commands to start the Installer where patchset_directory is the directory where you unzipped the patch set software:
$ cd /oracle9i/9207_patch/Disk1
$ ./runInstaller
* On the Welcome screen, click Next.
* On the Specify File Locations screen, click Browse next to the Path field in the Source section.
* Select the products.xml file from the stage directory where you unpacked the patch set files, then click Next. i,e
/oracle9i/9207_patch/Disk1/stage/products.xml
* In the Name field in the Destination section, select the name of the Oracle home that you want to update from the drop down list, then click Next.
Name: Home1
Path: /oracle9i.
* On the Summary screen, click Install.
* This screen lists all of the patches available for installation.
* When prompted, run the $ORACLE_HOME/root.sh script as the root user.
* On the End of Installation screen, click Exit, then click Yes to exit from the Installer.
Postinstallation Tasks
Required Postinstallation Tasks
Check SYSTEM Tablespace Size
If JServer is part of the installation ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace.
SQL> alter database datafile '/u02/oradata/testdb/SYSTEM01.DBF' resize 500m;
Database altered.
Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters
Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:
* Start the database:
SQL> STARTUP;
* If necessary, enter the following command to determine whether the system uses an initialization parameter file (initsid.ora) or a server parameter file (spfiledbname.ora):
SQL> SHOW PARAMETER PFILE;
* This command displays the name and location of the server parameter file or the initialization parameter file.
Determine the current values of these parameters:
SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL> SHOW PARAMETER JAVA_POOL_SIZE
* If the system is using a server parameter file:
If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;
* If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;
* If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).
* Shut down the database:
SQL> SHUTDOWN
Upgrade the Database
After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:
* Log in as the Oracle software owner (typically oracle).
su - oracle
* Start the Oracle Net listener as follows:
$ lsnrctl start
* For single-instance installations, use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:
$ sqlplus /nolog
SQL> CONNECT / AS SYSDBA
* Enter the following SQL*Plus commands:
SQL> STARTUP MIGRATE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF
* Review the patch.log file for errors and inspect the list of components that is displayed at the end of catpatch.sql script.
* This list provides the version and status of each SERVER component in the database.
* If necessary, rerun the catpatch.sql script after correcting any problems.
* Restart the database:
SQL> SHUTDOWN
SQL> STARTUP
* Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.
SQL> @?/rdbms/admin/utlrp.sql
Conclusion: It is importnat note that We have followed the above process as per our requirement and perticular limites to our test instance. But this is not the complete upgrade procedure, please refer the read me document of README for 4163445 " Oracle9i Patch Set Notes Release 2 (9.2.0.7) Patch Set 6 for Solaris Operating System (SPARC 64-bit) " .
Details:
Operating Version Details : SunOS libtest 5.10 Generic_118833-23 sun4u sparc SUNW,Sun-Fire
Oracle Version Details (Current) : Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
Oracle Instance Name : testdb
Patchset (to be applied on) : p4163445_9207_solaris64.zip (Downloaded from www.metalink.oracle.com)
Requirements for installing the patchset # 4163445
Oracle Universal Installer Version Requirements
The Oracle Universal Installer release must be 10.1.0.4 (This includes in the patchset).
System Requirements
* Operating System is Solaris 10 (But it can be Solaris 8 or 9)
* Oracle9i release 2 (9.2.0.1.0) or later
Preinstallation Tasks
Upgrade and SYS Schema
During an upgrade from release 9.2.0.1 or later, the catpatch.sql script can take a long time if there are statistics for the SYS schema. Delete the statistics on all of the objects in the SYS schema, and then recollect the statistics after normal database open, if necessary.
To drop and re-create the statistics.
SQL> EXECUTE DBMS_STATS.DELETE_SCHEMA_STATS('SYS');
SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
Download and Extract the Installation Software
To download and extract the patch set installation software:
Download and extract the p4163445_9207_solaris64.zip patch set installation archive to a directory " /oracle9i/9207_patch"
To unzip and extract the installation files:
$ unzip p4163445_9207_solaris64.zip
Set the ORACLE_HOME and ORACLE_SID Environment Variables
To set the ORACLE_HOME and ORACLE_SID environment variables:
Bourne, Bash, or Korn shell:
$ ORACLE_HOME=/oracle9i
$ ORACLE_SID=testdb
$ export ORACLE_HOME ORACLE_SID
C shell:
% setenv ORACLE_HOME /oracle9i
% setenv ORACLE_SID testdb
The oracle_home is the Oracle home directory where the Oracle9i installation that you want to upgrade is installed, and sid is the SID of the database that you want to upgrade.
Shut Down Oracle Databases
Shut down any existing Oracle database instances with normal or immediate priority.
SQL> SHUTDOWN IMMEDIATE;
Stop All Processes
Stop all listener and other processes running in the Oracle home directory where you want to install the patch set.
LSNRCTL> stop
Back Up the System
Oracle recommends that you create a backup of the Oracle9i installation before you install the patch set.
We took the backup of Oracle home and all the database files.
Installation Tasks
Installing the Patch Set Interactively
To install the patch set interactively:
* Log in as the Oracle software owner (typically oracle).
su - oracle
* If you are not installing the software on the local system, enter the following command to direct X applications to display on the local system:
Bourne, Bash, or Korn shell:
$ DISPLAY=libtest:0.0 ; export DISPLAY
C shell:
% setenv DISPLAY libtest:0.0
Here, local_host is the host name or IP address of the system that you want to use to display the Installer (your workstation or PC).
* Enter following commands to start the Installer where patchset_directory is the directory where you unzipped the patch set software:
$ cd /oracle9i/9207_patch/Disk1
$ ./runInstaller
* On the Welcome screen, click Next.
* On the Specify File Locations screen, click Browse next to the Path field in the Source section.
* Select the products.xml file from the stage directory where you unpacked the patch set files, then click Next. i,e
/oracle9i/9207_patch/Disk1/stage/products.xml
* In the Name field in the Destination section, select the name of the Oracle home that you want to update from the drop down list, then click Next.
Name: Home1
Path: /oracle9i.
* On the Summary screen, click Install.
* This screen lists all of the patches available for installation.
* When prompted, run the $ORACLE_HOME/root.sh script as the root user.
* On the End of Installation screen, click Exit, then click Yes to exit from the Installer.
Postinstallation Tasks
Required Postinstallation Tasks
Check SYSTEM Tablespace Size
If JServer is part of the installation ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace.
SQL> alter database datafile '/u02/oradata/testdb/SYSTEM01.DBF' resize 500m;
Database altered.
Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters
Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:
* Start the database:
SQL> STARTUP;
* If necessary, enter the following command to determine whether the system uses an initialization parameter file (initsid.ora) or a server parameter file (spfiledbname.ora):
SQL> SHOW PARAMETER PFILE;
* This command displays the name and location of the server parameter file or the initialization parameter file.
Determine the current values of these parameters:
SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL> SHOW PARAMETER JAVA_POOL_SIZE
* If the system is using a server parameter file:
If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;
* If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;
* If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).
* Shut down the database:
SQL> SHUTDOWN
Upgrade the Database
After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:
* Log in as the Oracle software owner (typically oracle).
su - oracle
* Start the Oracle Net listener as follows:
$ lsnrctl start
* For single-instance installations, use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:
$ sqlplus /nolog
SQL> CONNECT / AS SYSDBA
* Enter the following SQL*Plus commands:
SQL> STARTUP MIGRATE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF
* Review the patch.log file for errors and inspect the list of components that is displayed at the end of catpatch.sql script.
* This list provides the version and status of each SERVER component in the database.
* If necessary, rerun the catpatch.sql script after correcting any problems.
* Restart the database:
SQL> SHUTDOWN
SQL> STARTUP
* Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.
SQL> @?/rdbms/admin/utlrp.sql
Conclusion: It is importnat note that We have followed the above process as per our requirement and perticular limites to our test instance. But this is not the complete upgrade procedure, please refer the read me document of README for 4163445 " Oracle9i Patch Set Notes Release 2 (9.2.0.7) Patch Set 6 for Solaris Operating System (SPARC 64-bit) " .
Subscribe to:
Posts (Atom)
