My only 11G instance was running 11.1.0.6 and needed to be upgrade it to 11.1.0.7 to fix a couple of bugs. Apparently this patchkit also includes some new functionality. Before starting the upgrade I needed to do a bit of housekeeping. This instance is relatively small so I performed a full database backup as well as a full database export. I also created a pfile for good measure.
create pfile from spfile ;
I like to have a list of invalid objects to I can compare the before and after.
select object_name, owner, object_type
from all_objects where status like ‘INVALID’;
Next I checked the prerequisites for this upgrade. The time_zone check is a very important check that needs to be made.
select version from v$timezone_file;
I am running version 4 so I am okay to upgrade. Check out MetaLink Note 568125.1 if you are running anything other than version 4. Next I ran the script utlu111i.sql in the $OH/rdbms/admin directory. It is designed to let you know if there are issues to address prior to the upgrade. Next I shutdown my Oracle instance and performed the upgrade through the GUI installer. All went well. Then it was time to upgrade my instance.
sqlplus /nolog
startup upgrade
spool upgrade.log
@?/rdbms/admin/catupgrd.sql
spool off
This process took about 90 minutes to run so plan enough time for your upgrade. The script was run without errors. The final step of the script shutdown the database. I started the database normally, then shut it down normally, then started it up again normally. I generally take these extra steps just to ensure everything looks okay. To check the post-upgrade status this script is helpful
@?/rdbms/admin/utlu111s.sql
Next I needed to fix some invalid object which I did with this script.
@?/rdbms/admin/utlrp.sql
The final step to the upgrade process is to take a full database backup. Now I am fully upgraded to the latest version of Oracle. To check my version I ran.
select * from v$version
And then
select comp_name,status,version from dba_registry;
Friday, 27 January 2012
Wednesday, 18 January 2012
How to Install Oracle Enterprise Management Agent using silent install
Operating System: Solaris 10 SPARC 64 bit
Agent Version: 11.1.0.1.0
Where to download software:
Go to http://www.oracle.com/technetwork/oem/grid-control/downloads/agentsoft-090381.html
Accept the License Agreement
Under “Agent Software 64-bit platform” click on the “+” before Sun SPARC Solaris
Download the 11.1.0.1.0 Management Agent software
Transfer the zip file to the remote server where you intend to perform the installation.
Create a directory /home/oracle/em_agent_install in the remote server
Move the agent software zip file to the /home/oracle/em_agent_install
Unzip the downloaded agent software zip file on remote server
Editing Response File:
Go to
cd /home/oracle/em_agent_install/solaris/response
Make a backup of the additional_agent.rsp file
cp additional_agent.rsp additional_agent_backup.rsp
Edit the the following fields in the response file. For OMS_HOST make sure to set appropriate hostname where you have installed the Oracle Management Service. If you have chosen secure agent connections then set the “AGENT_REGISTRATION_PASSWORD” parameter. Also make sure to change the OMS_PORT value if you changed it during the Oracle Management Service (OMS) installation.
The parameter ORACLE_AGENT_HOME_LOCATION defines the location for the agent installation. A directory agent11g gets created under the path defined in the ORACLE_AGENT_HOME_LOCATION. If you want to install it in a different location, change this parameter.
SECURITY_UPDATES_VIA_MYORACLESUPPORT=FALSE
DECLINE_SECURITY_UPDATES=TRUE
INSTALL_UPDATES_SELECTION="skip"
ORACLE_AGENT_HOME_LOCATION="/opt/app/oracle/"
b_silentInstall=true
OMS_HOST="YOUR_OMS_HOST.YOUR_DOMAIN.com"
OMS_PORT="4889"
AGENT_REGISTRATION_PASSWORD="YOUR_REGISTRATION_PASSWORD"
FROM_LOCATION="/home/oracle/em_agent_install/solaris/agent/stage/products.xml"
Leave the rest of the parameters as-it-is and Save the file and exit the editor.
Silent Installation:
Go to
cd /home/oracle/em_agent_install/solaris/agent
Run the following command:
./runInstaller -silent -responsefile /home/oracle/em_agent_install/solaris/response/additional_agent.rsp
Agent Version: 11.1.0.1.0
Where to download software:
Go to http://www.oracle.com/technetwork/oem/grid-control/downloads/agentsoft-090381.html
Accept the License Agreement
Under “Agent Software 64-bit platform” click on the “+” before Sun SPARC Solaris
Download the 11.1.0.1.0 Management Agent software
Transfer the zip file to the remote server where you intend to perform the installation.
Create a directory /home/oracle/em_agent_install in the remote server
Move the agent software zip file to the /home/oracle/em_agent_install
Unzip the downloaded agent software zip file on remote server
Editing Response File:
Go to
cd /home/oracle/em_agent_install/solaris/response
Make a backup of the additional_agent.rsp file
cp additional_agent.rsp additional_agent_backup.rsp
Edit the the following fields in the response file. For OMS_HOST make sure to set appropriate hostname where you have installed the Oracle Management Service. If you have chosen secure agent connections then set the “AGENT_REGISTRATION_PASSWORD” parameter. Also make sure to change the OMS_PORT value if you changed it during the Oracle Management Service (OMS) installation.
The parameter ORACLE_AGENT_HOME_LOCATION defines the location for the agent installation. A directory agent11g gets created under the path defined in the ORACLE_AGENT_HOME_LOCATION. If you want to install it in a different location, change this parameter.
SECURITY_UPDATES_VIA_MYORACLESUPPORT=FALSE
DECLINE_SECURITY_UPDATES=TRUE
INSTALL_UPDATES_SELECTION="skip"
ORACLE_AGENT_HOME_LOCATION="/opt/app/oracle/"
b_silentInstall=true
OMS_HOST="YOUR_OMS_HOST.YOUR_DOMAIN.com"
OMS_PORT="4889"
AGENT_REGISTRATION_PASSWORD="YOUR_REGISTRATION_PASSWORD"
FROM_LOCATION="/home/oracle/em_agent_install/solaris/agent/stage/products.xml"
Leave the rest of the parameters as-it-is and Save the file and exit the editor.
Silent Installation:
Go to
cd /home/oracle/em_agent_install/solaris/agent
Run the following command:
./runInstaller -silent -responsefile /home/oracle/em_agent_install/solaris/response/additional_agent.rsp
How to Speed up ASM Disks Rebalancing Operation
How to Speed up ASM Disks Rebalancing Operation:
When I tried to drop two ASM disks (each ~ 100GB) from the ASM DISKGROUP I have noticed that the rebalance operation is taking more than an hour by querying the v$asm_operation.
SELECT group_number, operation, state, power, est_minutes FROM v$asm_operation;
Here is the ouput from the above command:
GROUP_NUMBER OPERA STAT POWER EST_MINUTES
------------ ----- ---- ---------- -----------
2 REBAL RUN 1 62
The total size of the diskgroup before dropping the two disks of interest is 500 GB of which 250 GB is free. So I decided to drop two ASM disks of each ~ 100 GB using ASMCA.
After doing little research, I altered the rebalance power for the diskgroup using the following command:
alter diskgroup DISKGROUP rebalance power 5;
The rebalance operation started again, this time the estimated time came down to about 15 minutes.
SELECT group_number, operation, state, power, est_minutes FROM v$asm_operation;
Here is the output from the above
GROUP_NUMBER OPERA STAT POWER EST_MINUTES
------------ ----- ---- ---------- -----------
2 REBAL RUN 5 15
Until the rebalance operation is finished, you shouldn’t touch the underlying physical disks.
When I tried to drop two ASM disks (each ~ 100GB) from the ASM DISKGROUP I have noticed that the rebalance operation is taking more than an hour by querying the v$asm_operation.
SELECT group_number, operation, state, power, est_minutes FROM v$asm_operation;
Here is the ouput from the above command:
GROUP_NUMBER OPERA STAT POWER EST_MINUTES
------------ ----- ---- ---------- -----------
2 REBAL RUN 1 62
The total size of the diskgroup before dropping the two disks of interest is 500 GB of which 250 GB is free. So I decided to drop two ASM disks of each ~ 100 GB using ASMCA.
After doing little research, I altered the rebalance power for the diskgroup using the following command:
alter diskgroup DISKGROUP rebalance power 5;
The rebalance operation started again, this time the estimated time came down to about 15 minutes.
SELECT group_number, operation, state, power, est_minutes FROM v$asm_operation;
Here is the output from the above
GROUP_NUMBER OPERA STAT POWER EST_MINUTES
------------ ----- ---- ---------- -----------
2 REBAL RUN 5 15
Until the rebalance operation is finished, you shouldn’t touch the underlying physical disks.
How to Apply Patch 12827731 (Rolling Fashion) – 11.2.0.2.4 Grid Patch Set Update
How to Apply Patch 12827731 – 11.2.0.2.4 Grid Patch Set Update
12827731 Grid Infrastructure Patch Set Update is a combination of patch numbers 12827731 and 1282776. This patch can be applied in a rolling fashion.
In order to apply the patch, the opatch utility version must be 11.2.0.1.5 or later.
How to check and update the version of opatch utility?
Assuming that you have the appropriate version of the opatch utility
Download patch p12827731_112020_Linux-x86-64.zip from support.metalink.com
Transfer the zip file to the server where you intend to apply the patch [Note: I created a directory /oratemp01/11202/software and transferred the zip file to this location]
cd to the patch directory and unzip the patch file
cd /oratemp01/11202/software
unzip p12827731_112020_Linux-x86-64.zip
You will notice that the file extracts into two sub-directories 12827731 and 12827726
First set the appropriate environment.
export ORACLE_HOME=/u01/app/11.2.0.2/grid
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
I ran the prereq check using the following command to determine whether any currently installed one-off patches conflict with the current PSU patch
opatch prereq CheckconflictAgainstOHWithDetail -phBaseDir ./12827731
Generating OCM Response File:
During the Installation of the Patch you will be prompted to enter the path for the Oracle Configuration Manager (OCM) Response file. Here is how to generate it.
export ORACLE_HOME=/u01/app/11.2.0.2/grid
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
cd $ORACLE_HOME/OPatch/ocm/bin
./emocmrsp
You will be prompted to enter you oracle support email address and password, if you wish to be informed about Oracle Securuity meastures enter them and hit enter, if not just hit enter. A response file gets created at /u01/app/11.2.0.2/grid/OPatch/ocm/bin/ocm.rsp
Procedure to Apply the Patch to Non-Shared Grid Infrastructure HOME and Non-Shared RDBMS HOME:
I am going to use opatch auto command to apply the patch first to a Non-shared Grid Infrastructure HOME and then to a Non-Shared Oracle RDBMS HOME
Shut down all the databases running under the to be patched 11.2.0.2.x Grid Home (Remeber ASM for 11gR2 runs under the Grid Infrastructure HOME)
Step-1: Apply the Patch to the Non-Shared Grid Infrastructure HOME
First Set the appropriate environment variables
export ORACLE_HOME=/u01/app/11.2.0.2/grid
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
Don’t shutdown the clusterware
Go to the patch directory
cd /oratemp01/11202/software
Issue the following command as root user or some one who has sudo privileges
sudo opatch auto -oh $ORACLE_HOME
[oracle@xyz software]$ sudo opatch auto -oh $ORACLE_HOME
Executing /usr/bin/perl /u01/app/11.2.0.2/grid/OPatch/crs/patch112.pl -patchdir /oratemp01/11202 -patchn software -oh /u01/app/11.2.0.2/grid -paramfile /u01/app/11.2.0.2/grid/crs/install/crsconfig_params
opatch auto log file location is /u01/app/11.2.0.2/grid/OPatch/crs/../../cfgtoollogs/opatchauto2011-11-16_14-33-59.log
Detected Oracle Clusterware install
Using configuration parameter file: /u01/app/11.2.0.2/grid/crs/install/crsconfig_params
OPatch is bundled with OCM, Enter the absolute OCM response file path:
/u01/app/11.2.0.2/grid/OPatch/ocm/bin/ocm.rsp
Successfully unlock /u01/app/11.2.0.2/grid
patch /oratemp01/11202/software/12827731 apply successful for home /u01/app/11.2.0.2/grid
patch /oratemp01/11202/software/12827726 apply successful for home /u01/app/11.2.0.2/grid
ACFS-9300: ADVM/ACFS distribution files found.
ACFS-9312: Existing ADVM/ACFS installation detected.
ACFS-9314: Removing previous ADVM/ACFS installation.
ACFS-9315: Previous ADVM/ACFS components successfully removed.
ACFS-9307: Installing requested ADVM/ACFS software.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9321: Creating udev for ADVM/ACFS.
ACFS-9323: Creating module dependencies - this may take some time.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9309: ADVM/ACFS installation correctness verified.
CRS-4123: Oracle High Availability Services has been started.
Do a opatch lsinventory to check if the patch has been applied to the 11.2.0.2.x GRID Infrastructure HOME
opatch lsinventory
Step-2: Apply the Patch to the 11.2.0.2.x Non-Shared RDBMS HOME
As mentioned earlier, in order to apply 12827731 patch the opatch utility version should be higher than 11.2.0.1.5.
How to check and update the version of opatch utility?
Shutdown all the databases associated with the 11.2.0.2.x Non-Shared RDBMS HOME
Set the appropriate environment variables
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/db11202_r
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
Enter the following command as root user or someone with sudo privileges
sudo opatch auto -oh $ORACLE_HOME
When prompted enter the full path to the OCM (oracle configuration manager) response file generated in the Step-1
Here is the update from the above command:
[oracle@xyz software]$ sudo opatch auto -oh $ORACLE_HOME
[sudo] password for oracle:
Executing /usr/bin/perl /u01/app/oracle/product/11.2.0/db11202_r/OPatch/crs/patch112.pl -patchdir /oratemp01/11202 -patchn software -oh /u01/app/oracle/product/11.2.0/db11202_r -paramfile /u01/app/11.2.0.2/grid/crs/install/crsconfig_params
opatch auto log file location is /u01/app/oracle/product/11.2.0/db11202_r/OPatch/crs/../../cfgtoollogs/opatchauto2011-11-16_14-49-41.log
Detected Oracle Clusterware install
Using configuration parameter file: /u01/app/11.2.0.2/grid/crs/install/crsconfig_params
OPatch is bundled with OCM, Enter the absolute OCM response file path:
/u01/app/11.2.0.2/grid/OPatch/ocm/bin/ocm.rsp
patch /oratemp01/11202/software/12827731/custom/server/12827731 apply successful for home /u01/app/oracle/product/11.2.0/db11202_r
patch /oratemp01/11202/software/12827726 apply successful for home /u01/app/oracle/product/11.2.0/db11202_r
Do a opatch lsinventory to check if the patch has been applied to the 11.2.0.2.x RDBMS HOME
opatch lsinventory
Perform similar steps on the remaining nodes one after another (rolling fashion)
Startup all the databases, associated with the patched RDBMS HOME
For each database set ORACLE_SID variable and load modified sql files into the database. Perform these steps only from one node.
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/db11202_r
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
At the sql prompt enter the following commands:
@?/rdbms/admin/catbundle.sql psu apply
QUIT
12827731 Grid Infrastructure Patch Set Update is a combination of patch numbers 12827731 and 1282776. This patch can be applied in a rolling fashion.
In order to apply the patch, the opatch utility version must be 11.2.0.1.5 or later.
How to check and update the version of opatch utility?
Assuming that you have the appropriate version of the opatch utility
Download patch p12827731_112020_Linux-x86-64.zip from support.metalink.com
Transfer the zip file to the server where you intend to apply the patch [Note: I created a directory /oratemp01/11202/software and transferred the zip file to this location]
cd to the patch directory and unzip the patch file
cd /oratemp01/11202/software
unzip p12827731_112020_Linux-x86-64.zip
You will notice that the file extracts into two sub-directories 12827731 and 12827726
First set the appropriate environment.
export ORACLE_HOME=/u01/app/11.2.0.2/grid
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
I ran the prereq check using the following command to determine whether any currently installed one-off patches conflict with the current PSU patch
opatch prereq CheckconflictAgainstOHWithDetail -phBaseDir ./12827731
Generating OCM Response File:
During the Installation of the Patch you will be prompted to enter the path for the Oracle Configuration Manager (OCM) Response file. Here is how to generate it.
export ORACLE_HOME=/u01/app/11.2.0.2/grid
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
cd $ORACLE_HOME/OPatch/ocm/bin
./emocmrsp
You will be prompted to enter you oracle support email address and password, if you wish to be informed about Oracle Securuity meastures enter them and hit enter, if not just hit enter. A response file gets created at /u01/app/11.2.0.2/grid/OPatch/ocm/bin/ocm.rsp
Procedure to Apply the Patch to Non-Shared Grid Infrastructure HOME and Non-Shared RDBMS HOME:
I am going to use opatch auto command to apply the patch first to a Non-shared Grid Infrastructure HOME and then to a Non-Shared Oracle RDBMS HOME
Shut down all the databases running under the to be patched 11.2.0.2.x Grid Home (Remeber ASM for 11gR2 runs under the Grid Infrastructure HOME)
Step-1: Apply the Patch to the Non-Shared Grid Infrastructure HOME
First Set the appropriate environment variables
export ORACLE_HOME=/u01/app/11.2.0.2/grid
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
Don’t shutdown the clusterware
Go to the patch directory
cd /oratemp01/11202/software
Issue the following command as root user or some one who has sudo privileges
sudo opatch auto -oh $ORACLE_HOME
[oracle@xyz software]$ sudo opatch auto -oh $ORACLE_HOME
Executing /usr/bin/perl /u01/app/11.2.0.2/grid/OPatch/crs/patch112.pl -patchdir /oratemp01/11202 -patchn software -oh /u01/app/11.2.0.2/grid -paramfile /u01/app/11.2.0.2/grid/crs/install/crsconfig_params
opatch auto log file location is /u01/app/11.2.0.2/grid/OPatch/crs/../../cfgtoollogs/opatchauto2011-11-16_14-33-59.log
Detected Oracle Clusterware install
Using configuration parameter file: /u01/app/11.2.0.2/grid/crs/install/crsconfig_params
OPatch is bundled with OCM, Enter the absolute OCM response file path:
/u01/app/11.2.0.2/grid/OPatch/ocm/bin/ocm.rsp
Successfully unlock /u01/app/11.2.0.2/grid
patch /oratemp01/11202/software/12827731 apply successful for home /u01/app/11.2.0.2/grid
patch /oratemp01/11202/software/12827726 apply successful for home /u01/app/11.2.0.2/grid
ACFS-9300: ADVM/ACFS distribution files found.
ACFS-9312: Existing ADVM/ACFS installation detected.
ACFS-9314: Removing previous ADVM/ACFS installation.
ACFS-9315: Previous ADVM/ACFS components successfully removed.
ACFS-9307: Installing requested ADVM/ACFS software.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9321: Creating udev for ADVM/ACFS.
ACFS-9323: Creating module dependencies - this may take some time.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9309: ADVM/ACFS installation correctness verified.
CRS-4123: Oracle High Availability Services has been started.
Do a opatch lsinventory to check if the patch has been applied to the 11.2.0.2.x GRID Infrastructure HOME
opatch lsinventory
Step-2: Apply the Patch to the 11.2.0.2.x Non-Shared RDBMS HOME
As mentioned earlier, in order to apply 12827731 patch the opatch utility version should be higher than 11.2.0.1.5.
How to check and update the version of opatch utility?
Shutdown all the databases associated with the 11.2.0.2.x Non-Shared RDBMS HOME
Set the appropriate environment variables
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/db11202_r
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
Enter the following command as root user or someone with sudo privileges
sudo opatch auto -oh $ORACLE_HOME
When prompted enter the full path to the OCM (oracle configuration manager) response file generated in the Step-1
Here is the update from the above command:
[oracle@xyz software]$ sudo opatch auto -oh $ORACLE_HOME
[sudo] password for oracle:
Executing /usr/bin/perl /u01/app/oracle/product/11.2.0/db11202_r/OPatch/crs/patch112.pl -patchdir /oratemp01/11202 -patchn software -oh /u01/app/oracle/product/11.2.0/db11202_r -paramfile /u01/app/11.2.0.2/grid/crs/install/crsconfig_params
opatch auto log file location is /u01/app/oracle/product/11.2.0/db11202_r/OPatch/crs/../../cfgtoollogs/opatchauto2011-11-16_14-49-41.log
Detected Oracle Clusterware install
Using configuration parameter file: /u01/app/11.2.0.2/grid/crs/install/crsconfig_params
OPatch is bundled with OCM, Enter the absolute OCM response file path:
/u01/app/11.2.0.2/grid/OPatch/ocm/bin/ocm.rsp
patch /oratemp01/11202/software/12827731/custom/server/12827731 apply successful for home /u01/app/oracle/product/11.2.0/db11202_r
patch /oratemp01/11202/software/12827726 apply successful for home /u01/app/oracle/product/11.2.0/db11202_r
Do a opatch lsinventory to check if the patch has been applied to the 11.2.0.2.x RDBMS HOME
opatch lsinventory
Perform similar steps on the remaining nodes one after another (rolling fashion)
Startup all the databases, associated with the patched RDBMS HOME
For each database set ORACLE_SID variable and load modified sql files into the database. Perform these steps only from one node.
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/db11202_r
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
At the sql prompt enter the following commands:
@?/rdbms/admin/catbundle.sql psu apply
QUIT
Saturday, 14 January 2012
Block Developers from Different TOOLS
CONNECT / AS SYSDBA;
CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT program INTO v_prog
FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don't Check SYS Connections
AND ROWNUM = 1; -- Parallel processes will have the same AUDSID's
IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR -- Business Objects
UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in
THEN
RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
END IF;
END;
/
SHOW ERRORS
CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT program INTO v_prog
FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don't Check SYS Connections
AND ROWNUM = 1; -- Parallel processes will have the same AUDSID's
IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR -- Business Objects
UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in
THEN
RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
END IF;
END;
/
SHOW ERRORS
ORA-06553: PLS-801: internal error [56319]
Few things to do
1) Running utlirp.sql (this is going to invalidate most (for me around 80,000 objects) and then recompiles them again) Took a helluva time.. but got it sorted out. in the end.
2) Had to run utlirp.sql when database is in migration mode. (remember I ported the db from 32 bit to 64 bit).
I am sure that this "IS A" solutions as now, I am getting the same error with 32bit version of oracle software. However with 64 bit I am fine. now. before this the error was with 64 bit and 32 bit was clean.
-------------- Other Scenario---------------------------------------------------------
Worked great, I had to restore a 32 bit database on a 64 bit system. After restore/recover operations with RMAN I did the following.
1) startup upgrade;
2) @?/rdbms/admin/utlirp.sql
3) shutdown immediate;
4) startup;
------------Other Scenario-----------------------------------------------------
Received the "ORA-06553: PLS-801: internal error [56319]" error after cloning a db, while trying to drop a user.
SQL> drop user QCSITEADMIN_DB0 cascade;
drop user QCSITEADMIN_DB0 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06553: PLS-801: internal error [56319]
and it got resolved after executing "utlirp.sql".
---------------------------------------------------My scenario -----------------
SYS @ avco >>conn volume/trace
ERROR:
ORA-06553: PLS-801: internal error [56327]
Error accessing package DBMS_APPLICATION_INFO
Connected.
VOLUME @ avco >>
VOLUME @ avco >>@utlirp.sql
VOLUME @ avco >>WHENEVER SQLERROR EXIT;
VOLUME @ avco >>DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if there the database was not opened in UPGRADE mode
DOC>
DOC> If you encounter this error, execute "SHUTDOWN", "STARTUP UPGRADE" and
DOC> re-execute utlirp.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>#
VOLUME @ avco >>SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance
2 WHERE status != 'OPEN MIGRATE';
SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance
*
ERROR at line 1:
ORA-00942: table or view does not exist
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - Production
With the Automatic Storage Management option
[oracle@VOL-ORATEST admin]$ s
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 24 12:35:48 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - Production
With the Automatic Storage Management option
SYS @ avco >>shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS @ avco >>startup upgrade
ORACLE instance started.
Total System Global Area 246910976 bytes
Fixed Size 1335752 bytes
Variable Size 192941624 bytes
Database Buffers 50331648 bytes
Redo Buffers 2301952 bytes
Database mounted.
Database opened.
SYS @ avco >>@utlirp.sql
......................
.........................
............................
PL/SQL procedure successfully completed.
SYS @ avco >>
SYS @ avco >>DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> utlirp.sql completed successfully. All PL/SQL objects in the
DOC> database have been invalidated.
DOC>
DOC> Shut down and restart the database in normal mode and run utlrp.sql to
DOC> recompile invalid objects.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SYS @ avco >>
SYS @ avco >>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS @ avco >>startup
ORACLE instance started.
Total System Global Area 246910976 bytes
Fixed Size 1335752 bytes
Variable Size 192941624 bytes
Database Buffers 50331648 bytes
Redo Buffers 2301952 bytes
Database mounted.
Database opened.
SYS @ avco >>
SYS @ avco >>connect volumexx/xxxxx
Connected.
VOLUME @ avco >>
1) Running utlirp.sql (this is going to invalidate most (for me around 80,000 objects) and then recompiles them again) Took a helluva time.. but got it sorted out. in the end.
2) Had to run utlirp.sql when database is in migration mode. (remember I ported the db from 32 bit to 64 bit).
I am sure that this "IS A" solutions as now, I am getting the same error with 32bit version of oracle software. However with 64 bit I am fine. now. before this the error was with 64 bit and 32 bit was clean.
-------------- Other Scenario---------------------------------------------------------
Worked great, I had to restore a 32 bit database on a 64 bit system. After restore/recover operations with RMAN I did the following.
1) startup upgrade;
2) @?/rdbms/admin/utlirp.sql
3) shutdown immediate;
4) startup;
------------Other Scenario-----------------------------------------------------
Received the "ORA-06553: PLS-801: internal error [56319]" error after cloning a db, while trying to drop a user.
SQL> drop user QCSITEADMIN_DB0 cascade;
drop user QCSITEADMIN_DB0 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06553: PLS-801: internal error [56319]
and it got resolved after executing "utlirp.sql".
---------------------------------------------------My scenario -----------------
SYS @ avco >>conn volume/trace
ERROR:
ORA-06553: PLS-801: internal error [56327]
Error accessing package DBMS_APPLICATION_INFO
Connected.
VOLUME @ avco >>
VOLUME @ avco >>@utlirp.sql
VOLUME @ avco >>WHENEVER SQLERROR EXIT;
VOLUME @ avco >>DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if there the database was not opened in UPGRADE mode
DOC>
DOC> If you encounter this error, execute "SHUTDOWN", "STARTUP UPGRADE" and
DOC> re-execute utlirp.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>#
VOLUME @ avco >>SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance
2 WHERE status != 'OPEN MIGRATE';
SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance
*
ERROR at line 1:
ORA-00942: table or view does not exist
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - Production
With the Automatic Storage Management option
[oracle@VOL-ORATEST admin]$ s
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 24 12:35:48 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - Production
With the Automatic Storage Management option
SYS @ avco >>shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS @ avco >>startup upgrade
ORACLE instance started.
Total System Global Area 246910976 bytes
Fixed Size 1335752 bytes
Variable Size 192941624 bytes
Database Buffers 50331648 bytes
Redo Buffers 2301952 bytes
Database mounted.
Database opened.
SYS @ avco >>@utlirp.sql
......................
.........................
............................
PL/SQL procedure successfully completed.
SYS @ avco >>
SYS @ avco >>DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> utlirp.sql completed successfully. All PL/SQL objects in the
DOC> database have been invalidated.
DOC>
DOC> Shut down and restart the database in normal mode and run utlrp.sql to
DOC> recompile invalid objects.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SYS @ avco >>
SYS @ avco >>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS @ avco >>startup
ORACLE instance started.
Total System Global Area 246910976 bytes
Fixed Size 1335752 bytes
Variable Size 192941624 bytes
Database Buffers 50331648 bytes
Redo Buffers 2301952 bytes
Database mounted.
Database opened.
SYS @ avco >>
SYS @ avco >>connect volumexx/xxxxx
Connected.
VOLUME @ avco >>
Monday, 2 January 2012
OCRCHECK utility
Have a Very Happy New Year 2012 to everyone.
OCRCHECK utility is used to verify the OCR integrity. The OCRCHECK utility displays the version of the OCR's block format, total space available and used space, OCRID, and the OCR locations that you have configured. OCRCHECK performs a block-by-block checksum operation for all of the blocks in all of the OCRs that you have configured. It also returns an individual status for each file as well as result for the overall OCR integrity check. The following is a sample of the OCRCHECK output:
Status of Oracle Cluster Registry is as follows :
Version : 2 Total space (kbytes) : 262144 Used space (kbytes) : 16256 Available space (kbytes) : 245888 ID : 1918913332 Device/File Name : /dev/raw/raw1 Device/File integrity check succeeded Device/File Name : /oradata/mirror.ocr Device/File integrity check succeeded Cluster registry integrity check succeeded
OCRCHECK creates a log file in the directory CRS_Home/log/hostname/client. To change amount of logging, edit the file CRS_Home/srvm/admin/ocrlog.ini
To remove an OCR location from your Oracle RAC environment:
Use the OCRCHECK utility to ensure that at least one OCR other than the OCR that you are removing is online.
> ocrcheck
Do not perform this OCR removal procedure unless there is at least one active OCR online.
Run the following comman on any node in the cluster to remove one copy of the OCR:
ocrconfig -replace ocr
This command updates the OCR configuration on all the nodes on which Oracle Clusterware is running.
Recovering the OCR
The first method uses automatically generated OCR file copies and the second method uses manually created OCR export files.
In event of a failure, before you attempt to restore the OCR, ensure that the OCR is unavailable. Run the following command to check the status of the OCR:
> ocrcheck
OCRCHECK utility is used to verify the OCR integrity. The OCRCHECK utility displays the version of the OCR's block format, total space available and used space, OCRID, and the OCR locations that you have configured. OCRCHECK performs a block-by-block checksum operation for all of the blocks in all of the OCRs that you have configured. It also returns an individual status for each file as well as result for the overall OCR integrity check. The following is a sample of the OCRCHECK output:
Status of Oracle Cluster Registry is as follows :
Version : 2 Total space (kbytes) : 262144 Used space (kbytes) : 16256 Available space (kbytes) : 245888 ID : 1918913332 Device/File Name : /dev/raw/raw1 Device/File integrity check succeeded Device/File Name : /oradata/mirror.ocr Device/File integrity check succeeded Cluster registry integrity check succeeded
OCRCHECK creates a log file in the directory CRS_Home/log/hostname/client. To change amount of logging, edit the file CRS_Home/srvm/admin/ocrlog.ini
To remove an OCR location from your Oracle RAC environment:
Use the OCRCHECK utility to ensure that at least one OCR other than the OCR that you are removing is online.
> ocrcheck
Do not perform this OCR removal procedure unless there is at least one active OCR online.
Run the following comman on any node in the cluster to remove one copy of the OCR:
ocrconfig -replace ocr
This command updates the OCR configuration on all the nodes on which Oracle Clusterware is running.
Recovering the OCR
The first method uses automatically generated OCR file copies and the second method uses manually created OCR export files.
In event of a failure, before you attempt to restore the OCR, ensure that the OCR is unavailable. Run the following command to check the status of the OCR:
> ocrcheck
Thursday, 29 December 2011
ORA-19809: limit exceeded for recovery files – db_recovery_file_dest_size and archiver error
ORA-19815: WARNING: db_recovery_file_dest_size of 42949672960 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Errors in file /u00/app/oracle/diag/rdbms/sid/SID/trace/SID_ora_20214.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 1063256064 bytes disk space from 42949672960 limit
ARCH: Error 19809 Creating archive log file to ‘+FRA’
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Errors in file /u00/app/oracle/diag/rdbms/sid/SID/trace/SID_ora_20214.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 1063256064 bytes disk space from 42949672960 limit
ARCH: Error 19809 Creating archive log file to ‘+FRA’
Monday, 26 December 2011
Finding the 10 largest objects in an Oracle database
col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner
, segment_name
, segment_type
, mb
from (
select owner
, segment_name
, segment_type
, bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc
)
where rownum < 11
/
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner
, segment_name
, segment_type
, mb
from (
select owner
, segment_name
, segment_type
, bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc
)
where rownum < 11
/
HOME ABOUT ORACLE SITES RSS Backing up your CRONTAB
If you are running a lot of scripts through CRON you may want to have a current backup. Since your CRON is simply a text schedule I prefer a daily email backup. It’s simple and does what I need. I add this line to my CRON:
00 0 * * * crontab -l > crontablist.txt;
mail -s "DW CRON - Quality Control" nitish@domain.com < crontablist.txt
Everyday at midnight I get an email with the contents of my CRON.
00 0 * * * crontab -l > crontablist.txt;
mail -s "DW CRON - Quality Control" nitish@domain.com < crontablist.txt
Everyday at midnight I get an email with the contents of my CRON.
Dynamically Delete Old Partitions
Are you looking to delete older partitions in an Oracle database? Here is a basic SQL query that will give you what you need.
select ‘ALTER TABLE ‘||table_owner||’.'||table_name
||’ DROP PARTITION ‘
||partition_name||’ UPDATE INDEXES;’
from dba_tab_partitions
where table_owner = ‘AVCO’
and table_name = ‘LOAN_DETAIL’
order by PARTITION_POSITION;
Modify to your specific needs.
select ‘ALTER TABLE ‘||table_owner||’.'||table_name
||’ DROP PARTITION ‘
||partition_name||’ UPDATE INDEXES;’
from dba_tab_partitions
where table_owner = ‘AVCO’
and table_name = ‘LOAN_DETAIL’
order by PARTITION_POSITION;
Modify to your specific needs.
Change a table’s default tablespace
Do you need to change a table’s default tablespace? It’s pretty simple:
ALTER TABLE {OWNER}.{TABLE NAME}
MODIFY DEFAULT ATTRIBUTES TABLESPACE {NEW TABLESPACE NAME};
This does not move the table it only changes the attribute for the default tablespace. This is helpful if you are adding partitions, but not specifying the tablespace.
ALTER TABLE {OWNER}.{TABLE NAME}
MODIFY DEFAULT ATTRIBUTES TABLESPACE {NEW TABLESPACE NAME};
This does not move the table it only changes the attribute for the default tablespace. This is helpful if you are adding partitions, but not specifying the tablespace.
Remove non-ASCII characters from a column
Do you need to remove special (non-ASCII) characters from a VarChar2 column in Oracle? Create this function:
CREATE OR REPLACE FUNCTION O1DW.RECTIFY_NON_ASCII(INPUT_STR IN VARCHAR2)
RETURN VARCHAR2
IS
str VARCHAR2(2000);
act number :=0;
cnt number :=0;
askey number :=0;
OUTPUT_STR VARCHAR2(2000);
begin
str:=’^'||TO_CHAR(INPUT_STR)||’^';
cnt:=length(str);
for i in 1 .. cnt loop
askey :=0;
select ascii(substr(str,i,1)) into askey
from dual;
if askey < 32 or askey >=127 then
str :=’^'||REPLACE(str, CHR(askey),”);
end if;
end loop;
OUTPUT_STR := trim(ltrim(rtrim(trim(str),’^'),’^'));
RETURN (OUTPUT_STR);
end;
/
CREATE OR REPLACE FUNCTION O1DW.RECTIFY_NON_ASCII(INPUT_STR IN VARCHAR2)
RETURN VARCHAR2
IS
str VARCHAR2(2000);
act number :=0;
cnt number :=0;
askey number :=0;
OUTPUT_STR VARCHAR2(2000);
begin
str:=’^'||TO_CHAR(INPUT_STR)||’^';
cnt:=length(str);
for i in 1 .. cnt loop
askey :=0;
select ascii(substr(str,i,1)) into askey
from dual;
if askey < 32 or askey >=127 then
str :=’^'||REPLACE(str, CHR(askey),”);
end if;
end loop;
OUTPUT_STR := trim(ltrim(rtrim(trim(str),’^'),’^'));
RETURN (OUTPUT_STR);
end;
/
Move and partitioned index to a new tablespace
To dynamically move a partitioned index to a new tablespace run the following SQL
select ‘Alter index ‘||INDEX_OWNER||’.'||INDEX_NAME||’
rebuild partition ‘||PARTITION_NAME||’ tablespace ABC_DETAIL_INDEX1;’
from dba_ind_partitions where TABLESPACE_NAME = ‘DW_ABC_DETAIL’;
select ‘Alter index ‘||INDEX_OWNER||’.'||INDEX_NAME||’
rebuild partition ‘||PARTITION_NAME||’ tablespace ABC_DETAIL_INDEX1;’
from dba_ind_partitions where TABLESPACE_NAME = ‘DW_ABC_DETAIL’;
Wednesday, 21 December 2011
How to resolve “Unable to create Patch Object” error?
FTU % opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./12419278
Invoking OPatch 11.1.0.6.0
Oracle Interim Patch Installer version 11.1.0.6.0 Copyright (c) 2007, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u02/app/oracle/product/11.2.0.1/FTU
Central Inventory : /u01/app/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.1.0.6.0
OUI version : 11.2.0.1.0
OUI location : /u02/app/oracle/product/11.2.0.1/FTU/oui Log file location : /u02/app/oracle/product/11.2.0.1/FTU/cfgtoollogs/opatch/opatch2011-11-05_01-50-34AM.log
Invoking prereq “checkconflictagainstohwithdetail”
The location “./12419278/README.html” is not a directory or a valid patch zip file.
Prereq “checkConflictAgainstOHWithDetail” not executed
PrereqSession failed: Unable to create Patch Object.
Exception occured : Patch ID is null.
OPatch failed with error code 73
We have two solutions for this problem
SOLUTION # 1
——————-
Move the text files like readme.html, readme.txt etc from the patch directory to some other location and try checking conflicts and it will be successful
But the problem is, this is not a permanent solution and just a workaround
SOLUTION # 2
——————–
This is the permanent solution for this problem. Cause for this error is we don’t have latest opatch version. As you can see in the output above my database version is 11.2.0.1, but my opatch version is 11.1.0.6. So, download latest opatch version from My Oracle Support with the help of patch # 6880880 and install it
Steps to install new opatch
1. copy the downloaded zip file for patch # 6880880 to $ORACLE_HOME
2. change location to ORACLE_HOME and rename the old OPatch directory
example : FTU% cd $ORACLE_HOME
FTU% mv OPatch OPatch.old
3. unzip the zip file which will create new OPatch directory
4. run below command to check if version is changed
FTU% $ORACLE_HOME/OPatch/opatch version
The above steps are also available in README file of the patch
Invoking OPatch 11.1.0.6.0
Oracle Interim Patch Installer version 11.1.0.6.0 Copyright (c) 2007, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u02/app/oracle/product/11.2.0.1/FTU
Central Inventory : /u01/app/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.1.0.6.0
OUI version : 11.2.0.1.0
OUI location : /u02/app/oracle/product/11.2.0.1/FTU/oui Log file location : /u02/app/oracle/product/11.2.0.1/FTU/cfgtoollogs/opatch/opatch2011-11-05_01-50-34AM.log
Invoking prereq “checkconflictagainstohwithdetail”
The location “./12419278/README.html” is not a directory or a valid patch zip file.
Prereq “checkConflictAgainstOHWithDetail” not executed
PrereqSession failed: Unable to create Patch Object.
Exception occured : Patch ID is null.
OPatch failed with error code 73
We have two solutions for this problem
SOLUTION # 1
——————-
Move the text files like readme.html, readme.txt etc from the patch directory to some other location and try checking conflicts and it will be successful
But the problem is, this is not a permanent solution and just a workaround
SOLUTION # 2
——————–
This is the permanent solution for this problem. Cause for this error is we don’t have latest opatch version. As you can see in the output above my database version is 11.2.0.1, but my opatch version is 11.1.0.6. So, download latest opatch version from My Oracle Support with the help of patch # 6880880 and install it
Steps to install new opatch
1. copy the downloaded zip file for patch # 6880880 to $ORACLE_HOME
2. change location to ORACLE_HOME and rename the old OPatch directory
example : FTU% cd $ORACLE_HOME
FTU% mv OPatch OPatch.old
3. unzip the zip file which will create new OPatch directory
4. run below command to check if version is changed
FTU% $ORACLE_HOME/OPatch/opatch version
The above steps are also available in README file of the patch
how to define RMAN retention policy time?
Many a times, I heard DBA’s complaining about RMAN retention policy through which they face some problems during restore or recovery.
For example a friend posted that his retention policy is set to recovery window of 90 days, but when tried to restore the database to 30 days back date, RMAN saying recovery till that time is not possible. he wondered why this cannot be done if he has retention of 90 days.
Here is the answer for the same….
we all know that RMAN will store backup metadata in control file’s reusable location if recovery catalog is not configured. Generally this information will be stored for 7 days and can be changed by modifying CONTROLFILE_RECORD_KEEP_TIME parameter.
whenever we define retention policy of RMAN either to recovery window or redundancy to some X value, we need to remember that RMAN still follow the value defined for CONTROLFILE_RECOD_KEEP_TIME. That means, if recovery window is set to 30 days, but the above parameter is set to 7 days (default), you cannot recover beyond 7 days… which is very unfortunate…
Right ! so lets see what can be done for this?
we need to define CONTROLFILE_RECORD_KEEP_TIME always a higher value than what we set for retention policy. As per the formula, it should be
CONTROLFILE_RECORD_KEEP_TIME=retention policy value+ level 0 backup interval+ 1
let me throw some example for easy understanding…
If suppose your retention time is set to recovery window of 90 days and a weekly full level 0 backup is scheduled, then CONTROLFILE_RECORD_KEEP_TIME = 90 (retention policy) + 7 (level 0 backup interval) + 1 = 98
Note: In worst case, atleast you should set that same (equal to) as retention policy value
What happens if I don’t set to higher value?
In such case, RMAN will overwrite the content of backup metadata prior to obsolete them by RMAN and even though physically backup pieces are existing, you cannot use them
For example a friend posted that his retention policy is set to recovery window of 90 days, but when tried to restore the database to 30 days back date, RMAN saying recovery till that time is not possible. he wondered why this cannot be done if he has retention of 90 days.
Here is the answer for the same….
we all know that RMAN will store backup metadata in control file’s reusable location if recovery catalog is not configured. Generally this information will be stored for 7 days and can be changed by modifying CONTROLFILE_RECORD_KEEP_TIME parameter.
whenever we define retention policy of RMAN either to recovery window or redundancy to some X value, we need to remember that RMAN still follow the value defined for CONTROLFILE_RECOD_KEEP_TIME. That means, if recovery window is set to 30 days, but the above parameter is set to 7 days (default), you cannot recover beyond 7 days… which is very unfortunate…
Right ! so lets see what can be done for this?
we need to define CONTROLFILE_RECORD_KEEP_TIME always a higher value than what we set for retention policy. As per the formula, it should be
CONTROLFILE_RECORD_KEEP_TIME=retention policy value+ level 0 backup interval+ 1
let me throw some example for easy understanding…
If suppose your retention time is set to recovery window of 90 days and a weekly full level 0 backup is scheduled, then CONTROLFILE_RECORD_KEEP_TIME = 90 (retention policy) + 7 (level 0 backup interval) + 1 = 98
Note: In worst case, atleast you should set that same (equal to) as retention policy value
What happens if I don’t set to higher value?
In such case, RMAN will overwrite the content of backup metadata prior to obsolete them by RMAN and even though physically backup pieces are existing, you cannot use them
Tuesday, 20 December 2011
Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2
Applies to:
Oracle Server - Enterprise Edition - Version: 11.2.0.2 and later [Release: 11.2 and later ]
Oracle Server - Standard Edition - Version: 11.2.0.2 and later [Release: 11.2 and later]
Oracle Server - Personal Edition - Version: 11.2.0.2 and later [Release: 11.2 and later]
Information in this document applies to any platform.
What is being announced?
Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2
What has changed?
Packaging: Starting with the first patch set for Oracle Database 11g Release 2 (11.2.0.2), Oracle Database patch sets are full installations of the Oracle Database software. In past releases, Oracle Database patch sets consisted of a set of files that replaced files in an existing Oracle home. Beginning with Oracle Database 11g Release 2, patch sets are full installations that replace existing installations.
Because Oracle Database 11g Release 2 and later patch sets are a full installation, Oracle patch sets are now released as several installation bundles, just as are the base releases:
Oracle Database Server
Oracle Grid Infrastructure
Oracle Database Client
Companion/examples
Oracle Gateways
Simplified new installation: Because the release 11.2.0.2 patch set is a full installation package, if you are installing Oracle Database on a system without an existing Oracle Database installation, then you can simply install the release 11.2.0.2 patch set. You are no longer required to install the base release, and then apply the patch set.
Upgrade process: Beginning with the release 11.2.0.2 patch set, you have two ways to apply a patch set:
Out-of-place upgrade – This is Oracle's recommended way to apply a patch set. You install the patch set into a new, separate Oracle home location. After you install the patch upgrade, you then migrate the Oracle Database from the older Oracle home. The patch set upgrade is now the same process to upgrade from one version to another. Oracle recommends that you perform an out-of-place patch set upgrade, because this patch set application option requires much less downtime, and is safer because it does not require patching an ORACLE_HOME that is already being used in production. However, you must ensure that you have sufficient free disk space to accommodate two Oracle home directories at the same time.
In-place upgrade – You install the patch set into an existing Oracle home location. Oracle recommends that you select this option only if you do not have sufficient free disk space to perform an out-of-place upgrade, as the upgrade removes the existing Oracle installation. This patch option requires less disk space, but requires more time, and is riskier, because if you encounter an installation failure, then you must recover the entire existing Oracle home from a backup. If you choose this more risky option, then before you begin the patch installation, complete the following tasks:
Make a complete backup of your existing Oracle home
Read through the entire Upgrade Guide section dealing with in-place upgrades
Note: All Oracle Grid Infrastructure patch set upgrades must be out-of-place upgrades, in which you install the patch set into a new Grid home. In-place patch set upgrades are not supported.
Direct upgrade from previous releases: You can upgrade from a previous Oracle Database release directly to the latest patch set, without having to install the base release. For example, if you want to upgrade from Oracle Database 10g Release 2, or Oracle Database 11g Release 1, then you can upgrade directly to Oracle Database 11g Release 2, patch set 2 (11.2.0.2) using an out-of-place upgrade. Oracle now supports direct upgrades to the release 11.2.0.2 patch set from any release where direct upgrade to the base release (11.2.0.1) is supported, as well as direct upgrades from 11.2.0.1 to 11.2.0.2.
New Features Available in Patch Sets
In addition to the usual set of fixes, the release 11.2.0.2 patch set contains a small number of features that complete the Database 11g Release 2 feature set. The new features fall into the following five categories:
General improvements
Oracle Automatic Storage Management Cluster File System (ACFS) improvements
Quality of Service Management
Database Replay
Management
How to Obtain the Patch Set
As with all previous patch sets, the release 11.2.0.2 patch set is available for download from the Patches tab on My Oracle Support.
Important Changes to Note:
Disk space requirement: Because the out-of-place upgrade requires the installation of a new Oracle home, you must have enough free disk space to accommodate the new Oracle home, as well as disk space needed for temporary files during installation.
ORACLE_HOME location change: When using the out-of-place upgrade path, the location of your Oracle home is different after you complete the upgrade. You must change any direct references to the old location of your Oracle home (such as in /etc/oratab or in tnsnames.ora) to the new location, and update user environment variables for the Oracle home.
In-place upgrade process differs from previous patch set installation processes: Even though it seems similar, the in-place upgrade process for the release 11.2.0.2 patch set differs from previous patch set installation procedures. It is very important to read the entire in-place upgrade process before you begin to install the release 11.2.0.2 patch set, because the preinstallation steps are different.
Frequently Asked Questions
Q. Is this really a patch set?
A. Yes – This patch set still consists primarily of incremental bug fixes to the base release (11.2.0.1) but with different packaging. While new features are included in 11.2.0.2, the number of new features is very small, and is included to complete the 11.2 feature set.
Q. Why did Oracle make this change?
A. In response to customer feedback, Oracle is making this change for several reasons:
Out-of-place upgrade has been a best practice of many customers for patch set application for years. Out-of-place upgrades reduce downtime, because you can complete the initial installation and configuration of the upgrade before you take down the existing installation. Out-of-place patch upgrades are easier, safer, and provide faster fallback if needed: If you encounter issues with the new installation, then simply point clients back to the original Oracle Database installation Oracle home, and restart the database.
Out-of-place upgrades allow customers running multiple instances out of one Oracle home to upgrade each instance individually rather than having to upgrade all instances sharing an Oracle home at the same time (and incur downtime on them all simultaneously).
Full installations provide a better customer experience during the upgrade. By creating a full installation package that uses the same installation process as a base release (as opposed to a differential install of the changes objects), Oracle is able to use the same tools to build and test the patch set installation as Oracle uses with the base release. Improved testing enables a smoother, more trouble-free upgrade process that uses significantly less down time.
Full installations provide an easier upgrade for customers moving to release 11.2.0.2 from releases earlier than Oracle Database 11g release 2. Instead of having to install the base release, and then applying a patch release, you can now upgrade to the latest release in one step.
Q. Is this new packaging unique to the 11.2.0.2 patch set, or will Oracle follow this model for future patch sets?
A. This will be the new model going forward. Oracle will release patch set release 11.2.0.3 and later patch sets as full installation packages. Direct out-of-place upgrade is the Oracle best practices recommendation for patch set upgrades.
Q. There are many new features in 11.2.0.2. Are patch sets not just for bug fixes?
A. Oracle intends to maintain the focus of patch sets as a fix delivery vehicle. Oracle has included a very small number of new features in past patch sets. In this case, after the release of Oracle Database 11g release 2 (11.2.0.1), Oracle completed development of a small number of features that are closely related to the 11.2 release, and Oracle is including these features so that you can use them now, rather than wait for their inclusion in next major release.
Reference Note: Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2 [ID 1189783.1]
Oracle Server - Enterprise Edition - Version: 11.2.0.2 and later [Release: 11.2 and later ]
Oracle Server - Standard Edition - Version: 11.2.0.2 and later [Release: 11.2 and later]
Oracle Server - Personal Edition - Version: 11.2.0.2 and later [Release: 11.2 and later]
Information in this document applies to any platform.
What is being announced?
Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2
What has changed?
Packaging: Starting with the first patch set for Oracle Database 11g Release 2 (11.2.0.2), Oracle Database patch sets are full installations of the Oracle Database software. In past releases, Oracle Database patch sets consisted of a set of files that replaced files in an existing Oracle home. Beginning with Oracle Database 11g Release 2, patch sets are full installations that replace existing installations.
Because Oracle Database 11g Release 2 and later patch sets are a full installation, Oracle patch sets are now released as several installation bundles, just as are the base releases:
Oracle Database Server
Oracle Grid Infrastructure
Oracle Database Client
Companion/examples
Oracle Gateways
Simplified new installation: Because the release 11.2.0.2 patch set is a full installation package, if you are installing Oracle Database on a system without an existing Oracle Database installation, then you can simply install the release 11.2.0.2 patch set. You are no longer required to install the base release, and then apply the patch set.
Upgrade process: Beginning with the release 11.2.0.2 patch set, you have two ways to apply a patch set:
Out-of-place upgrade – This is Oracle's recommended way to apply a patch set. You install the patch set into a new, separate Oracle home location. After you install the patch upgrade, you then migrate the Oracle Database from the older Oracle home. The patch set upgrade is now the same process to upgrade from one version to another. Oracle recommends that you perform an out-of-place patch set upgrade, because this patch set application option requires much less downtime, and is safer because it does not require patching an ORACLE_HOME that is already being used in production. However, you must ensure that you have sufficient free disk space to accommodate two Oracle home directories at the same time.
In-place upgrade – You install the patch set into an existing Oracle home location. Oracle recommends that you select this option only if you do not have sufficient free disk space to perform an out-of-place upgrade, as the upgrade removes the existing Oracle installation. This patch option requires less disk space, but requires more time, and is riskier, because if you encounter an installation failure, then you must recover the entire existing Oracle home from a backup. If you choose this more risky option, then before you begin the patch installation, complete the following tasks:
Make a complete backup of your existing Oracle home
Read through the entire Upgrade Guide section dealing with in-place upgrades
Note: All Oracle Grid Infrastructure patch set upgrades must be out-of-place upgrades, in which you install the patch set into a new Grid home. In-place patch set upgrades are not supported.
Direct upgrade from previous releases: You can upgrade from a previous Oracle Database release directly to the latest patch set, without having to install the base release. For example, if you want to upgrade from Oracle Database 10g Release 2, or Oracle Database 11g Release 1, then you can upgrade directly to Oracle Database 11g Release 2, patch set 2 (11.2.0.2) using an out-of-place upgrade. Oracle now supports direct upgrades to the release 11.2.0.2 patch set from any release where direct upgrade to the base release (11.2.0.1) is supported, as well as direct upgrades from 11.2.0.1 to 11.2.0.2.
New Features Available in Patch Sets
In addition to the usual set of fixes, the release 11.2.0.2 patch set contains a small number of features that complete the Database 11g Release 2 feature set. The new features fall into the following five categories:
General improvements
Oracle Automatic Storage Management Cluster File System (ACFS) improvements
Quality of Service Management
Database Replay
Management
How to Obtain the Patch Set
As with all previous patch sets, the release 11.2.0.2 patch set is available for download from the Patches tab on My Oracle Support.
Important Changes to Note:
Disk space requirement: Because the out-of-place upgrade requires the installation of a new Oracle home, you must have enough free disk space to accommodate the new Oracle home, as well as disk space needed for temporary files during installation.
ORACLE_HOME location change: When using the out-of-place upgrade path, the location of your Oracle home is different after you complete the upgrade. You must change any direct references to the old location of your Oracle home (such as in /etc/oratab or in tnsnames.ora) to the new location, and update user environment variables for the Oracle home.
In-place upgrade process differs from previous patch set installation processes: Even though it seems similar, the in-place upgrade process for the release 11.2.0.2 patch set differs from previous patch set installation procedures. It is very important to read the entire in-place upgrade process before you begin to install the release 11.2.0.2 patch set, because the preinstallation steps are different.
Frequently Asked Questions
Q. Is this really a patch set?
A. Yes – This patch set still consists primarily of incremental bug fixes to the base release (11.2.0.1) but with different packaging. While new features are included in 11.2.0.2, the number of new features is very small, and is included to complete the 11.2 feature set.
Q. Why did Oracle make this change?
A. In response to customer feedback, Oracle is making this change for several reasons:
Out-of-place upgrade has been a best practice of many customers for patch set application for years. Out-of-place upgrades reduce downtime, because you can complete the initial installation and configuration of the upgrade before you take down the existing installation. Out-of-place patch upgrades are easier, safer, and provide faster fallback if needed: If you encounter issues with the new installation, then simply point clients back to the original Oracle Database installation Oracle home, and restart the database.
Out-of-place upgrades allow customers running multiple instances out of one Oracle home to upgrade each instance individually rather than having to upgrade all instances sharing an Oracle home at the same time (and incur downtime on them all simultaneously).
Full installations provide a better customer experience during the upgrade. By creating a full installation package that uses the same installation process as a base release (as opposed to a differential install of the changes objects), Oracle is able to use the same tools to build and test the patch set installation as Oracle uses with the base release. Improved testing enables a smoother, more trouble-free upgrade process that uses significantly less down time.
Full installations provide an easier upgrade for customers moving to release 11.2.0.2 from releases earlier than Oracle Database 11g release 2. Instead of having to install the base release, and then applying a patch release, you can now upgrade to the latest release in one step.
Q. Is this new packaging unique to the 11.2.0.2 patch set, or will Oracle follow this model for future patch sets?
A. This will be the new model going forward. Oracle will release patch set release 11.2.0.3 and later patch sets as full installation packages. Direct out-of-place upgrade is the Oracle best practices recommendation for patch set upgrades.
Q. There are many new features in 11.2.0.2. Are patch sets not just for bug fixes?
A. Oracle intends to maintain the focus of patch sets as a fix delivery vehicle. Oracle has included a very small number of new features in past patch sets. In this case, after the release of Oracle Database 11g release 2 (11.2.0.1), Oracle completed development of a small number of features that are closely related to the 11.2 release, and Oracle is including these features so that you can use them now, rather than wait for their inclusion in next major release.
Reference Note: Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2 [ID 1189783.1]
Step By Step Manual Database Upgrade to 11.2.0.2
Compatibility Matrix
Minimum Version of the database that can be directly upgraded to Oracle 11g Release 2
Source Database Target Database
9.2.0.8 or higher 11.2.x
10.1.0.5 or higher 11.2.x
10.2.0.2 or higher 11.2.x
11.1.0.6 or higher 11.2.x
The following database version will require an indirect upgrade path.
Source Database ---> Upgrade Path for Target Database--->Target Database
7.3.3 (or lower)-----> 7.3.4 ---> 9.2.0.8 ---->11.2.x
8.0.5 (or lower)----> 8.0.6 ---> 9.2.0.8 ---->11.2.x
8.1.7 (or lower)----> 8.1.7.4---> 10.2.0.4---->11.2.x
9.0.1.3 (or lower)----> 9.0.1.4-- ->10.2.0.4---->11.2.x
9.2.0.7(or lower)---->9.2.0.8---->11.2.x
Here I am upgrading my Oracle 10.2.0.4 database to Oracle 11.2.0.2
1. Copy Pre upgrade Information gathering Script:
Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2) Oracle home directory.
Copy the Pre-Upgrade Information Tool (utlu112i.sql) and utltzuv2.sql from the Oracle Database 11g Release 2 (11.2) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the temporary directory on your system.
$ORACLE_HOME/rdbms/admin/utlu112i.sql
2. Execute Pre Upgrade Script:
Should be change to the directory where utlu112i.sql had been copied in the previous step.
Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges. Then run and spool the utlu112i.sql file. Please note that the database should be started using the Source Oracle Home.
$ sqlplus '/ as sysdba'
SQL> spool upgrade_info.log
SQL> @utlu112i.sql
SQL> spool off
SQL>
Check the spool file and examine the output of the upgrade information tool.
3. Check for the integrity of the source database:
Check for the integrity of the source database prior to starting the upgrade by downloading and running dbupgdiag.sql script from below My Oracle Support article
Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) (Avoid this step if don’t have support access)
If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql
After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.
4. Check that National Characterset:
Check that National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 and AL16UTF16.
select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';
If this is UTF8 or AL16UTF16 then no action is needed.
If is not UTF8 or AL16UTF16 then refer the following article
Note 276914.1 The National Character Set in Oracle 9i and 10g.
5. Optimizer Statistics:
When upgrading to Oracle Database 11g Release 2 (11.2), optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade
$ sqlplus "/as sysdba"
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
6. Ensure that no files need media recovery:
Sql> SELECT * FROM v$recover_file;
7. Stop the listener for the database:
Make pfile from spfile;
$ lsnrctl stop
8. Suhtdown the Database:
Shutdown the database.
$ sqlplus "/as sysdba"
SQL> shutdown immediate;
9. Back Up the Database:
1- Perform Cold Backup
(or)
2- Take a backup using RMAN
Connect to RMAN:
rman "target / nocatalog"
RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT '%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE TO '';
}
10 Backup and change pfile:
Make a backup of the init.ora file.
Comment out obsolete parameters
* The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST.
Note: Once the Parameter file is modified as per your requirement, copy the file to $ORACLE_HOME/dbs (11g Oracle Home )
11 Set Environment Variables:
If your operating system is UNIX then complete this step, else skip to next Step.
1. Make sure the following environment variables point to the Oracle 11g Release directories:
- ORACLE_BASE
- ORACLE_HOME
- PATH
$ export ORACLE_HOME=
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_BASE=
Note : If ORACLE_BASE is not known, after setting Path towards 11g Oracle Home, execute 'orabase', which will point the location of base.
$ orabase
/uo1/app/oracle
2. Update the oratab entry, to set the new ORACLE_HOME pointing towards ORCL and disable automatic startup
Sample /etc/oratab
#orcl:/opt/oracle/product/10.2/db_1:N
orcl:/opt/oracle/product/11.2/db_1:N
Note : After /etc/oratab is updated to have sid and Oracle Home (11.2), you can execute oraenv (/usr/local/bin/oraenv) and set the environment. The input has to be the sid which is entered in /etc/oratab against 11g home.
for Instance,
[oracle@localhost ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl
The Oracle base for ORACLE_HOME=/opt/oracle/product/11.2/db_1 is /u01/app/oracle
[oracle@localhost ~]$
12 Upgrade Database:
At the operating system prompt, change to the $ORACLE_HOME/rdbms/admin directory of 11gR2 Oracle Home.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE
Set the system to spool results to a log file for later verification after the upgrade is completed and start the upgrade script.
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> spool off
These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle Database software. Also, if you encountered a message listing obsolete initialization parameters when you started the database for upgrade, then remove the obsolete initialization parameters from the parameter file before restarting. If necessary, convert the SPFILE to a PFILE so you can edit the file to delete parameters.
Post Upgrade Steps
13 Post Upgrade:
Start the database and run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu112s.sql which provides a summary of the upgrade at the end of the spool log. It displays the status of the database components in the upgraded database and the time required to complete each component upgrade. Any errors that occur during the upgrade are listed with each component and must be addressed
$ sqlplus "/as sysdba"
SQL> STARTUP
SQL> @utlu112s.sql
14 Recompile Invalid Objects:
This script can be run concurrently with utlrp.sql. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.
SQL> @utlrp.sql
15 Check for the integrity of the source database:
Check for the integrity of the upgraded database by running dbupgdiag.sql script from below Metalink article
Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)
If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.
After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.
16 Configure & Start Listener.ora :
Modify the listener.ora file:
For the upgraded instance(s) modify the ORACLE_HOME parameter to point to the new ORACLE_HOME. Start the listener :
lsnrctl start
17 Crosscheck Environment Variables:
Set Environment Variables
1. Make sure the following environment variables point to the Oracle 11g Release directories:
- ORACLE_BASE
- ORACLE_HOME
- PATH
Also check that your oratab file and any client scripts that set the value of ORACLE_HOME point to the Oracle Database 11g Release 2 (11.2) home.
Note : If you are upgrading a cluster database, then perform these checks on all nodes in which this cluster database has instances configured.
2. Modify /etc/oratab entry to use automatic startup
SID:ORACLE_HOME:Y
For Instance,
orcl:/opt/oracle/product/11.2/db_1:Y
18 Spfile from Pfile:
Edit init.ora:
- If you changed the CLUSTER_DATABASE parameter prior the upgrade set it back to TRUE
- Migrate your initialization parameter file to a server parameter file.
Create a server parameter file with a initialization parameter file
SQL> create spfile from pfile;
This will create a spfile as a copy of the init.ora file located in $ORACLE_HOME/dbs (UNIX) & %ORACLE_HOME%\database (Windows).
19 Start the Database with spfile:
Shutdown the database:
Sql> shutdown immediate
Now start the database it will user spfile
Sql> startup
Check the Alert log file for any Error.
Database is ready to use now.
Minimum Version of the database that can be directly upgraded to Oracle 11g Release 2
Source Database Target Database
9.2.0.8 or higher 11.2.x
10.1.0.5 or higher 11.2.x
10.2.0.2 or higher 11.2.x
11.1.0.6 or higher 11.2.x
The following database version will require an indirect upgrade path.
Source Database ---> Upgrade Path for Target Database--->Target Database
7.3.3 (or lower)-----> 7.3.4 ---> 9.2.0.8 ---->11.2.x
8.0.5 (or lower)----> 8.0.6 ---> 9.2.0.8 ---->11.2.x
8.1.7 (or lower)----> 8.1.7.4---> 10.2.0.4---->11.2.x
9.0.1.3 (or lower)----> 9.0.1.4-- ->10.2.0.4---->11.2.x
9.2.0.7(or lower)---->9.2.0.8---->11.2.x
Here I am upgrading my Oracle 10.2.0.4 database to Oracle 11.2.0.2
1. Copy Pre upgrade Information gathering Script:
Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2) Oracle home directory.
Copy the Pre-Upgrade Information Tool (utlu112i.sql) and utltzuv2.sql from the Oracle Database 11g Release 2 (11.2) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the temporary directory on your system.
$ORACLE_HOME/rdbms/admin/utlu112i.sql
2. Execute Pre Upgrade Script:
Should be change to the directory where utlu112i.sql had been copied in the previous step.
Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges. Then run and spool the utlu112i.sql file. Please note that the database should be started using the Source Oracle Home.
$ sqlplus '/ as sysdba'
SQL> spool upgrade_info.log
SQL> @utlu112i.sql
SQL> spool off
SQL>
Check the spool file and examine the output of the upgrade information tool.
3. Check for the integrity of the source database:
Check for the integrity of the source database prior to starting the upgrade by downloading and running dbupgdiag.sql script from below My Oracle Support article
Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) (Avoid this step if don’t have support access)
If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql
After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.
4. Check that National Characterset:
Check that National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 and AL16UTF16.
select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';
If this is UTF8 or AL16UTF16 then no action is needed.
If is not UTF8 or AL16UTF16 then refer the following article
Note 276914.1 The National Character Set in Oracle 9i and 10g.
5. Optimizer Statistics:
When upgrading to Oracle Database 11g Release 2 (11.2), optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade
$ sqlplus "/as sysdba"
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
6. Ensure that no files need media recovery:
Sql> SELECT * FROM v$recover_file;
7. Stop the listener for the database:
Make pfile from spfile;
$ lsnrctl stop
8. Suhtdown the Database:
Shutdown the database.
$ sqlplus "/as sysdba"
SQL> shutdown immediate;
9. Back Up the Database:
1- Perform Cold Backup
(or)
2- Take a backup using RMAN
Connect to RMAN:
rman "target / nocatalog"
RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT '%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE TO '';
}
10 Backup and change pfile:
Make a backup of the init.ora file.
Comment out obsolete parameters
* The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST.
Note: Once the Parameter file is modified as per your requirement, copy the file to $ORACLE_HOME/dbs (11g Oracle Home )
11 Set Environment Variables:
If your operating system is UNIX then complete this step, else skip to next Step.
1. Make sure the following environment variables point to the Oracle 11g Release directories:
- ORACLE_BASE
- ORACLE_HOME
- PATH
$ export ORACLE_HOME=
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_BASE=
Note : If ORACLE_BASE is not known, after setting Path towards 11g Oracle Home, execute 'orabase', which will point the location of base.
$ orabase
/uo1/app/oracle
2. Update the oratab entry, to set the new ORACLE_HOME pointing towards ORCL and disable automatic startup
Sample /etc/oratab
#orcl:/opt/oracle/product/10.2/db_1:N
orcl:/opt/oracle/product/11.2/db_1:N
Note : After /etc/oratab is updated to have sid and Oracle Home (11.2), you can execute oraenv (/usr/local/bin/oraenv) and set the environment. The input has to be the sid which is entered in /etc/oratab against 11g home.
for Instance,
[oracle@localhost ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl
The Oracle base for ORACLE_HOME=/opt/oracle/product/11.2/db_1 is /u01/app/oracle
[oracle@localhost ~]$
12 Upgrade Database:
At the operating system prompt, change to the $ORACLE_HOME/rdbms/admin directory of 11gR2 Oracle Home.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE
Set the system to spool results to a log file for later verification after the upgrade is completed and start the upgrade script.
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> spool off
These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle Database software. Also, if you encountered a message listing obsolete initialization parameters when you started the database for upgrade, then remove the obsolete initialization parameters from the parameter file before restarting. If necessary, convert the SPFILE to a PFILE so you can edit the file to delete parameters.
Post Upgrade Steps
13 Post Upgrade:
Start the database and run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu112s.sql which provides a summary of the upgrade at the end of the spool log. It displays the status of the database components in the upgraded database and the time required to complete each component upgrade. Any errors that occur during the upgrade are listed with each component and must be addressed
$ sqlplus "/as sysdba"
SQL> STARTUP
SQL> @utlu112s.sql
14 Recompile Invalid Objects:
This script can be run concurrently with utlrp.sql. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.
SQL> @utlrp.sql
15 Check for the integrity of the source database:
Check for the integrity of the upgraded database by running dbupgdiag.sql script from below Metalink article
Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)
If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.
After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.
16 Configure & Start Listener.ora :
Modify the listener.ora file:
For the upgraded instance(s) modify the ORACLE_HOME parameter to point to the new ORACLE_HOME. Start the listener :
lsnrctl start
17 Crosscheck Environment Variables:
Set Environment Variables
1. Make sure the following environment variables point to the Oracle 11g Release directories:
- ORACLE_BASE
- ORACLE_HOME
- PATH
Also check that your oratab file and any client scripts that set the value of ORACLE_HOME point to the Oracle Database 11g Release 2 (11.2) home.
Note : If you are upgrading a cluster database, then perform these checks on all nodes in which this cluster database has instances configured.
2. Modify /etc/oratab entry to use automatic startup
SID:ORACLE_HOME:Y
For Instance,
orcl:/opt/oracle/product/11.2/db_1:Y
18 Spfile from Pfile:
Edit init.ora:
- If you changed the CLUSTER_DATABASE parameter prior the upgrade set it back to TRUE
- Migrate your initialization parameter file to a server parameter file.
Create a server parameter file with a initialization parameter file
SQL> create spfile from pfile;
This will create a spfile as a copy of the init.ora file located in $ORACLE_HOME/dbs (UNIX) & %ORACLE_HOME%\database (Windows).
19 Start the Database with spfile:
Shutdown the database:
Sql> shutdown immediate
Now start the database it will user spfile
Sql> startup
Check the Alert log file for any Error.
Database is ready to use now.
How to see free space on unix system.
[oracle@itc-799 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 100G 72G 24G 76% /
/dev/sda2 341G 205G 119G 64% /data
/dev/sda1 92M 12M 76M 14% /boot
tmpfs 2.0G 0 2.0G 0% /dev/shm
df -h is command to check space usage on unix system, type this command on unix prompt and the out will be like above detail. In the above example, we have two mount points “/” and “/data” which we can use. In local environment usually we have two or one mount point.
Let’s talk about these mount points one by one.
/dev/sda3 100G 72G 24G 76% /
This mount point is known as root mount point and has total 100G of space out of which 72G is used and 24G is available.
/dev/sda2 341G 205G 119G 64% /data
This mount point is known as data mount point as given name and have 341G of space out of which 205G is used and 119G is free and the used percentage is 64%.
Some systems may have more mount points like
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 100G 72G 24G 76% /
/dev/sda2 341G 205G 119G 64% /data
/dev/sda4 341G 205G 119G 64% /data1
/dev/sda1 92M 12M 76M 14% /boot
tmpfs 2.0G 0 2.0G 0% /dev/shm
This system has there mount points “/” , “/data” and “/data1”.
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 100G 72G 24G 76% /
/dev/sda2 341G 205G 119G 64% /data
/dev/sda1 92M 12M 76M 14% /boot
tmpfs 2.0G 0 2.0G 0% /dev/shm
df -h is command to check space usage on unix system, type this command on unix prompt and the out will be like above detail. In the above example, we have two mount points “/” and “/data” which we can use. In local environment usually we have two or one mount point.
Let’s talk about these mount points one by one.
/dev/sda3 100G 72G 24G 76% /
This mount point is known as root mount point and has total 100G of space out of which 72G is used and 24G is available.
/dev/sda2 341G 205G 119G 64% /data
This mount point is known as data mount point as given name and have 341G of space out of which 205G is used and 119G is free and the used percentage is 64%.
Some systems may have more mount points like
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 100G 72G 24G 76% /
/dev/sda2 341G 205G 119G 64% /data
/dev/sda4 341G 205G 119G 64% /data1
/dev/sda1 92M 12M 76M 14% /boot
tmpfs 2.0G 0 2.0G 0% /dev/shm
This system has there mount points “/” , “/data” and “/data1”.
Moving between two Oracle Homes
I have two Oracle versions installed on my system. Oracle 11.2.0.1 and Oracle 11.2.0.2.
Here are the steps how to switch between them.
Make Oracle 11.2.0.1 as Oracle Home
oracle@suse32:~> cd /opt/oracle/product/11.2/db_1/bin/
oracle@suse32:/opt/oracle/product/11.2/db_1/bin> export ORACLE_HOME=/opt/oracle/product/11.2/db_1 product/11.2/db_1
oracle@suse32:/opt/oracle/product/11.2/db_1/bin> ./sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 7 23:47:37 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> exit;
oracle@suse32:/opt/oracle/product> ls
11.2 11.2.0.2
Make Oracle 11.2.0.2 as Oracle Home
oracle@suse32:/opt/oracle/product> cd 11.2.0.2/
oracle@suse32:/opt/oracle/product/11.2.0.2> cd db_1/bin/
oracle@suse32:/opt/oracle/product/11.2.0.2/db_1/bin> export ORACLE_HOME=/opt/oracle/product/11.2.0.2/db_1
oracle@suse32:/opt/oracle/product/11.2.0.2/db_1/bin> ./sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Production on Mon Nov 7 23:58:40 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL>
Here are the steps how to switch between them.
Make Oracle 11.2.0.1 as Oracle Home
oracle@suse32:~> cd /opt/oracle/product/11.2/db_1/bin/
oracle@suse32:/opt/oracle/product/11.2/db_1/bin> export ORACLE_HOME=/opt/oracle/product/11.2/db_1 product/11.2/db_1
oracle@suse32:/opt/oracle/product/11.2/db_1/bin> ./sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 7 23:47:37 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> exit;
oracle@suse32:/opt/oracle/product> ls
11.2 11.2.0.2
Make Oracle 11.2.0.2 as Oracle Home
oracle@suse32:/opt/oracle/product> cd 11.2.0.2/
oracle@suse32:/opt/oracle/product/11.2.0.2> cd db_1/bin/
oracle@suse32:/opt/oracle/product/11.2.0.2/db_1/bin> export ORACLE_HOME=/opt/oracle/product/11.2.0.2/db_1
oracle@suse32:/opt/oracle/product/11.2.0.2/db_1/bin> ./sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Production on Mon Nov 7 23:58:40 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL>
Subscribe to:
Posts (Atom)