1. When you moved oracle binary files from one
ORACLE_HOME server to another server then which oracle utility will be used to
make this new ORACLE_HOME usable?
Relink all.
2. In which months oracle release CPU patches?
JAN, APR, JUL, OCT
3. When we applying single Patch, can you use opatch utility?
Yes, you can use Opatch in case of single patch. The only type of patch that cannot be used with OPatch is a patchset.
4. Is it possible to apply OPATCH without downtime?
As you know for apply patch your database and listener must be down. When you apply OPTACH it will update your current ORACLE_HOME. Thus coming to your question to the point in fact it is not possible without or zero downtime in case of single instance but in RAC you can Apply Opatch without downtime as there will be more separate ORACLE_HOME and more separate instances (running once instance on each ORACLE_HOME).
5. You have collection of patch (nearly 100 patches) or patchset. How can you apply only one patch from it?
With Napply itself (by providing patch location and specific patch id) you can apply only one patch from a collection of extracted patch. For more information check the opatchutilNApply –help. It will give you clear picture.
For Example:
opatchutilnapply<patch_location> -id 9 -skip_subset -skip_duplicate
This will apply only the patch id 9 from the patch location and will skip duplicate and subset of patch installed in your ORACLE_HOME.
6. If both CPU and PSU are available for given version which one, you will prefer to apply?
From the above discussion it is clear once you apply the PSU then the recommended way is to apply the next PSU only. In fact, no need to apply CPU on the top of PSU as PSU contain CPU (If you apply CPU over PSU will considered you are trying to rollback the PSU and will require more effort in fact). So if you have not decided or applied any of the patches then, I will suggest you to go to use PSU patches. For more details refer: Oracle Products [ID 1430923.1], ID 1446582.1
7. PSU is superset of CPU then why someone choose to apply a CPU rather than a PSU?
CPUs are smaller and more focused than PSU and mostly deal with security issues. It seems to be theoretically more consecutive approach and can cause less trouble than PSU as it has less code changing in it. Thus any one who is concerned only with security fixes and not functionality fixes, CPU may be good approach.
8. How to Download Patches, Patchset or Opatch from metalink?
If you are using latest support.oracle.com then after login to metalink Dashboard
- Click on "Patches & Updates" tab
- On the left sidebar click on "Latest Patchsets" under "Oracle Server/Tools".
- A new window will appear.
- Just mouseover on your product in the "Latest Oracle Server/Tools Patchsets" page.
- Corresponding oracle platform version will appear. Then simply choose the patchset version and click on that.
- You will go the download page. From the download page you can also change your platform and patchset version.
Relink all.
2. In which months oracle release CPU patches?
JAN, APR, JUL, OCT
3. When we applying single Patch, can you use opatch utility?
Yes, you can use Opatch in case of single patch. The only type of patch that cannot be used with OPatch is a patchset.
4. Is it possible to apply OPATCH without downtime?
As you know for apply patch your database and listener must be down. When you apply OPTACH it will update your current ORACLE_HOME. Thus coming to your question to the point in fact it is not possible without or zero downtime in case of single instance but in RAC you can Apply Opatch without downtime as there will be more separate ORACLE_HOME and more separate instances (running once instance on each ORACLE_HOME).
5. You have collection of patch (nearly 100 patches) or patchset. How can you apply only one patch from it?
With Napply itself (by providing patch location and specific patch id) you can apply only one patch from a collection of extracted patch. For more information check the opatchutilNApply –help. It will give you clear picture.
For Example:
opatchutilnapply<patch_location> -id 9 -skip_subset -skip_duplicate
This will apply only the patch id 9 from the patch location and will skip duplicate and subset of patch installed in your ORACLE_HOME.
6. If both CPU and PSU are available for given version which one, you will prefer to apply?
From the above discussion it is clear once you apply the PSU then the recommended way is to apply the next PSU only. In fact, no need to apply CPU on the top of PSU as PSU contain CPU (If you apply CPU over PSU will considered you are trying to rollback the PSU and will require more effort in fact). So if you have not decided or applied any of the patches then, I will suggest you to go to use PSU patches. For more details refer: Oracle Products [ID 1430923.1], ID 1446582.1
7. PSU is superset of CPU then why someone choose to apply a CPU rather than a PSU?
CPUs are smaller and more focused than PSU and mostly deal with security issues. It seems to be theoretically more consecutive approach and can cause less trouble than PSU as it has less code changing in it. Thus any one who is concerned only with security fixes and not functionality fixes, CPU may be good approach.
8. How to Download Patches, Patchset or Opatch from metalink?
If you are using latest support.oracle.com then after login to metalink Dashboard
- Click on "Patches & Updates" tab
- On the left sidebar click on "Latest Patchsets" under "Oracle Server/Tools".
- A new window will appear.
- Just mouseover on your product in the "Latest Oracle Server/Tools Patchsets" page.
- Corresponding oracle platform version will appear. Then simply choose the patchset version and click on that.
- You will go the download page. From the download page you can also change your platform and patchset version.
9. How to Apply patch in Oracle?
1. You MUST read the Readme.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch.
2.Make sure you have a good backup of database.
3. Make a note of all Invalid objects in the database prior to the patch.
4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.
5. You MUST Backup your oracle Home and Inventory
tar -cvf $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip> Backup_Software_Version.tar.gz
6. Unzip the patch in $ORACLE_HOME/patches
7. cd to the patch direcory and do opatch -apply to apply the patch.
8. Read the output/log file to make sure there were no errors.
OR
1. You MUST read the Readme.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch.
2.Make sure you have a good backup of database.
3. Make a note of all Invalid objects in the database prior to the patch.
4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.
5. You MUST Backup your oracle Home and Inventory
tar -cvf $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip> Backup_Software_Version.tar.gz
6. Unzip the patch in $ORACLE_HOME/patches
7. cd to the patch direcory and do opatch -apply to apply the patch.
8. Read the output/log file to make sure there were no errors.
OR
1. Download
the required Patch from Metalink based on OS Bit Version and DB Version.
2. Need to down the database before applying patch.
3. Unzip and Apply the Patch using ”opatch apply” command.On successfully applied of patch you will see successful message “OPatch succeeded.“, Crosscheck your patch is applied by using “opatchlsinventory” command .
4. Each patch has a unique ID, the command to rollback a patch is “opatch rollback -id <patch no.>” command.On successfully applied of patch you will see successful message “OPatch succeeded.“, Crosscheck your patch is applied by using “opatchlsinventory” command .
5. Patch file format will be like, “p<patch no.>_<db version>_<os>.zip”
6. We can check the opatch version using “opatch -version” command.
7. Generally, takes 2 minutes to apply a patch.
8. To get latest Opatch version download “patch 6880880 - latest opatch tool”, it contains OPatch directory.
9. Contents of downloaded patches will be like “etc,files directories and a README file”
10. Log file for Opatch utility can be found at $ORACLE_HOME/cfgtoollogs/opatch
11. OPatch also maintains an index of the commands executed with OPatch and the log files associated with it in the history.txt file located in the <ORACLE_HOME>/cfgtoollogs/opatch directory.
12. Starting with the 11.2.0.2 patch set, Oracle Database patch sets are full installations of the Oracle Database software. This means that you do not need to install Oracle Database 11g Release 2 (11.2.0.1) before installing Oracle Database 11g Release 2 (11.2.0.2).
13. Direct upgrade to Oracle 10g is only supported if your database is running one of the following releases: 8.0.6, 8.1.7, 9.0.1, or 9.2.0. If not, you will have to upgrade the database to one of these releases or use a different upgrade option (like export/ import).
14.Direct upgrades to 11g are possible from existing databases with versions 9.2.0.4+, 10.1.0.2+ or 10.2.0.1+. Upgrades from other versions are supported only via intermediate upgrades to a supported upgrade version.
2. Need to down the database before applying patch.
3. Unzip and Apply the Patch using ”opatch apply” command.On successfully applied of patch you will see successful message “OPatch succeeded.“, Crosscheck your patch is applied by using “opatchlsinventory” command .
4. Each patch has a unique ID, the command to rollback a patch is “opatch rollback -id <patch no.>” command.On successfully applied of patch you will see successful message “OPatch succeeded.“, Crosscheck your patch is applied by using “opatchlsinventory” command .
5. Patch file format will be like, “p<patch no.>_<db version>_<os>.zip”
6. We can check the opatch version using “opatch -version” command.
7. Generally, takes 2 minutes to apply a patch.
8. To get latest Opatch version download “patch 6880880 - latest opatch tool”, it contains OPatch directory.
9. Contents of downloaded patches will be like “etc,files directories and a README file”
10. Log file for Opatch utility can be found at $ORACLE_HOME/cfgtoollogs/opatch
11. OPatch also maintains an index of the commands executed with OPatch and the log files associated with it in the history.txt file located in the <ORACLE_HOME>/cfgtoollogs/opatch directory.
12. Starting with the 11.2.0.2 patch set, Oracle Database patch sets are full installations of the Oracle Database software. This means that you do not need to install Oracle Database 11g Release 2 (11.2.0.1) before installing Oracle Database 11g Release 2 (11.2.0.2).
13. Direct upgrade to Oracle 10g is only supported if your database is running one of the following releases: 8.0.6, 8.1.7, 9.0.1, or 9.2.0. If not, you will have to upgrade the database to one of these releases or use a different upgrade option (like export/ import).
14.Direct upgrades to 11g are possible from existing databases with versions 9.2.0.4+, 10.1.0.2+ or 10.2.0.1+. Upgrades from other versions are supported only via intermediate upgrades to a supported upgrade version.
10. Patching Oracle Software with OPatch ?
opatchnapply<patch_location> -skip_subset -skip_duplicate
OPatch skips duplicate patches and subset patches (patches under <patch_location> that are subsets of patches installed in the Oracle home).
11. Oracle version 10.2.0.4.0 what does each number refers to? OR What Do The Release Numbers Represent?
Oracle version number refers: (reading from left to right)
10 – Major database release number
2 – Database Maintenance release number
0 – Application server release number
4 – Component Specific release number /Patch set release
0 – Platform specific release number
Note: Starting with release 9.2, maintenance releases of Oracle Database are denoted by a change to the second digit of a release number. In previous releases, the third digits indicated a particular maintenance release.
10 – Major database release number
2 – Database Maintenance release number
0 – Application server release number
4 – Component Specific release number /Patch set release
0 – Platform specific release number
Note: Starting with release 9.2, maintenance releases of Oracle Database are denoted by a change to the second digit of a release number. In previous releases, the third digits indicated a particular maintenance release.
12. What is
rolling upgrade?
It is a new ASM feature from Database 11g.ASM instances in
Oracle database 11g release(from 11.1) can be upgraded or patched using rolling
upgrade feature. This enables us to patch or upgrade ASM nodes in a clustered
environment without affecting database availability.During a rolling upgrade we
can maintain a functional cluster while one or more of the nodes in the cluster
are running in different software versions. Rolling upgrade can be used only
for Oracle database 11g releases(from 11.1).
13. Steps to Upgrade in Oracle ?
Manual upgrade which involves the following steps:
1.Backup the database.
2.In UNIX/Linux environments, set the $ORACLE_HOME and $PATH variables to point to the new 11g Oracle home.
3.Analyze the existing instance using the "$ORACLE_HOME/rdbms/admin/utlu111i.sql" script.
4.Start the original database using the STARTUP UPGRADE command and proceed with the upgrade by running the "$ORACLE_HOME/rdbms/admin/catupgrd.sql" script.
5.Recompile invalid objects.
6.Restart the database.
7.Run the "$ORACLE_HOME/rdbms/admin/utlu111s.sql" script and check the result of the upgrade.
8.Troubleshoot any issues or abort the upgrade.
14. What happens when you give "STARTUP UPGRADE"?
$sqlplus "/as sysdba"
SQL> STARTUP UPGRADE
The UPGRADE keyword enables you to open a database based on an earlier Oracle Database release. It also restricts logons to AS SYSDBA sessions, disables system triggers, and performs additional operations that prepare the environment for the upgrade.
You might be required to use the PFILE option to specify the location of your initialization parameter file.
Once the database is started in upgrade mode, only queries on fixed views execute without errors until after the catupgrd.sql script is run. Before running catupgrd.sql, queries on any other view or the use of PL/SQL returns an error.
15. What is the difference between startup Upgrade and Migrate ?
startup migrate: Used to upgrade a database till 9i.
Startup Upgrade: From 10G we are using startup upgrade to upgrade database.
16. What happens internally when you use startup upgrade/migrate?
It will adjust few database (init) parameters (irrespective of what you have defined) automatically to certain values in order to run upgrade scripts smoothly.
in other way..it will issue few alter statements to set certain parameters which are required to complete the upgrade scripts without any issues.
17. Common issues faced in Upgrade ( Error is related to timezone file)
Started database in upgrade mode and fired catupgrd.sql :
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 6413680640 bytes
Fixed Size 2160112 bytes
Variable Size 1946159632 bytes
Database Buffers 4429185024 bytes
Redo Buffers 36175872 bytes
Database mounted.
Database opened.
SQL> @catupgrd.sql
DOC>#######################################################################
DOC>#######################################################################
DOC> The first time this script is run, there should be no error messages
DOC> generated; all normal upgrade error messages are suppressed.
DOC>
DOC> If this script is being re-run after correcting some problem, then
DOC> expect the following error which is not automatically suppressed:
DOC>
DOC> ORA-00001: unique constraint () violated
DOC>#
FROM registry$database
*
ERROR at line 2:
ORA-00942: table or view does not exist
This error is related to timezone file which must be version 4 for Oracle version 11g.If timezone is not version 4 than patch needs to be applied. Query to check timezone file is:
SQL> select * from v$timezone_file;
FILENAME VERSION
———— ———-
timezlrg.dat 4
So I had correct version.I remember applying patch before upgrade.I got lucky because patch existed for version 10.2.0.3. If there is no patch for your Oracle versions than patch can be download for similar version and applied manually.
Instructions are below:
1. Download the identified patch.
2. Unzip the patch, and locate the 2 files timezone.dat and timezlrg.dat in the “files/oracore/zoneinfo” directory of the uncompressed patch (or from the relevant .jar file of a patchset). If there is also a readme.txt in this location then make a note of this as well.
3. Backup your existing files in $ORACLE_HOME/oracore/zoneinfo – THIS CAN BE VITAL, DO NOT SKIP.
Note:
Before going on with step 4, make sure the current files are not in use.
On Windows the files will simply refuse to be removed when the are in use.
On Unix replacing the files whilst they are in use can cause the files to become corrupt. Use the fuser command before replacing the files to make sure they are not in use.
4. Copy the 2 .dat files and possibly the readme.txt file that were found in step 2 into the $ORACLE_HOME/oracore/zoneinfo directory.
5. Restart the database (in case of installation on a database), or restart the client applications (in case of client install). Note that the database did not need to be down before the time zone files were applied, but it does need to be restarted afterwards.
http://indiandba.blogspot.in/2012/01/error-is-related-to-timezone-file.html
Manual upgrade which involves the following steps:
1.Backup the database.
2.In UNIX/Linux environments, set the $ORACLE_HOME and $PATH variables to point to the new 11g Oracle home.
3.Analyze the existing instance using the "$ORACLE_HOME/rdbms/admin/utlu111i.sql" script.
4.Start the original database using the STARTUP UPGRADE command and proceed with the upgrade by running the "$ORACLE_HOME/rdbms/admin/catupgrd.sql" script.
5.Recompile invalid objects.
6.Restart the database.
7.Run the "$ORACLE_HOME/rdbms/admin/utlu111s.sql" script and check the result of the upgrade.
8.Troubleshoot any issues or abort the upgrade.
14. What happens when you give "STARTUP UPGRADE"?
$sqlplus "/as sysdba"
SQL> STARTUP UPGRADE
The UPGRADE keyword enables you to open a database based on an earlier Oracle Database release. It also restricts logons to AS SYSDBA sessions, disables system triggers, and performs additional operations that prepare the environment for the upgrade.
You might be required to use the PFILE option to specify the location of your initialization parameter file.
Once the database is started in upgrade mode, only queries on fixed views execute without errors until after the catupgrd.sql script is run. Before running catupgrd.sql, queries on any other view or the use of PL/SQL returns an error.
15. What is the difference between startup Upgrade and Migrate ?
startup migrate: Used to upgrade a database till 9i.
Startup Upgrade: From 10G we are using startup upgrade to upgrade database.
16. What happens internally when you use startup upgrade/migrate?
It will adjust few database (init) parameters (irrespective of what you have defined) automatically to certain values in order to run upgrade scripts smoothly.
in other way..it will issue few alter statements to set certain parameters which are required to complete the upgrade scripts without any issues.
17. Common issues faced in Upgrade ( Error is related to timezone file)
Started database in upgrade mode and fired catupgrd.sql :
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 6413680640 bytes
Fixed Size 2160112 bytes
Variable Size 1946159632 bytes
Database Buffers 4429185024 bytes
Redo Buffers 36175872 bytes
Database mounted.
Database opened.
SQL> @catupgrd.sql
DOC>#######################################################################
DOC>#######################################################################
DOC> The first time this script is run, there should be no error messages
DOC> generated; all normal upgrade error messages are suppressed.
DOC>
DOC> If this script is being re-run after correcting some problem, then
DOC> expect the following error which is not automatically suppressed:
DOC>
DOC> ORA-00001: unique constraint () violated
DOC>#
FROM registry$database
*
ERROR at line 2:
ORA-00942: table or view does not exist
This error is related to timezone file which must be version 4 for Oracle version 11g.If timezone is not version 4 than patch needs to be applied. Query to check timezone file is:
SQL> select * from v$timezone_file;
FILENAME VERSION
———— ———-
timezlrg.dat 4
So I had correct version.I remember applying patch before upgrade.I got lucky because patch existed for version 10.2.0.3. If there is no patch for your Oracle versions than patch can be download for similar version and applied manually.
Instructions are below:
1. Download the identified patch.
2. Unzip the patch, and locate the 2 files timezone.dat and timezlrg.dat in the “files/oracore/zoneinfo” directory of the uncompressed patch (or from the relevant .jar file of a patchset). If there is also a readme.txt in this location then make a note of this as well.
3. Backup your existing files in $ORACLE_HOME/oracore/zoneinfo – THIS CAN BE VITAL, DO NOT SKIP.
Note:
Before going on with step 4, make sure the current files are not in use.
On Windows the files will simply refuse to be removed when the are in use.
On Unix replacing the files whilst they are in use can cause the files to become corrupt. Use the fuser command before replacing the files to make sure they are not in use.
4. Copy the 2 .dat files and possibly the readme.txt file that were found in step 2 into the $ORACLE_HOME/oracore/zoneinfo directory.
5. Restart the database (in case of installation on a database), or restart the client applications (in case of client install). Note that the database did not need to be down before the time zone files were applied, but it does need to be restarted afterwards.
http://indiandba.blogspot.in/2012/01/error-is-related-to-timezone-file.html
18. What is an
interim patch (one-off patch)?
Interim patches are bug
fixes that are made available to customers in response to specific bugs.
They require a
particular base release or patchset to be installed before they can be applied.
They generally address
specific bugs for a particular customer.
These patches are not
versioned and are generally made available in a future patchset as well as the
next product release.
Clusterware interim
patches are rare, they usually are build on top of the latest PSU (at the time)
and include the entire PSU they were built on. The same does not hold true for
database interim patches, they usually do not include a PSU.
19. How to
install/Rollback an interim patch ?
Interim patches can be
installed/Rollbacked using the patching tool called opatch.Opatch can be found
under the Oracle_Home/Opatch (by default)
20. How can you list
all the installed patches in your oracle home?
Interim patches can be listed using the patching tool called
opatch.Opatch can be found under the Oracle_Home/Opatch (by default). You can
use the command % opatch lsinventory -all
21. What are the
contents of an interim patch ?
The interim patches have
the following contents :
Patch Metadata: This
contains information on the patch ID, the bugs fixed, the files affected, and
the actions to be performed.
Payload: This contains
the files that will be modified by OPatch.
Custom Scripts: Pre-processing and post-processing scripts that
needs to be run before and after patching.
22. What is the
difference between an interim patch and a Patchset ?
Patchset are a small
collection of files that are copied over an existing installation. They are
associated to particular versions of Oracle Products. Patch set , when applied
to the correct version of an installed results in an upgraded version of the
product.
Interim patches are bug
fixes that are made available to customers in response to specific bugs. They
require a particular base release or patchset to be installed before they can
be applied. They generally specific bugs for a particular customer.These
patches are not versioned and are generally made available in a future patchset
23. Is CPU (Critical
Patch Update) an Interim patch ?
Yes, CPU is an Interim
patch. A Critical Patch Update (CPU) is a collection of patches for multiple
security vulnerabilities. Oracle provides CPUs for all product offerings on a
quarterly schedule. CPU is cumulative patch.
24. What are the different methods to upgrade your database to 11gR1
or 11gR2?
The different upgrade methods you can use to upgrade your database are :
Database Upgrade Assistant (DBUA)
-- the simplest method
Manual Upgrade--
If you want the greatest control over the upgrade process, or you are looking
for the best debugging mode if there is a problem, then use the
Manual Upgrade method.
Export/Import
Data Copying
If you are migrating across OS platforms then Transportable Tablespaces (TTS) or Streams
may help.
Document 733205.1 Migration of an Oracle Database Across OS
Platforms
25. What is the best
way to upgrade to 11gR1 or 11gR2?
There is no best way to
upgrade to 11g Release 1 (11.1) for all situations. The DBUA is the recommended
method for upgrading a database if you do not have a need for using one of the
other available methods.
26. What are the
steps to upgrade a database to 11gR1 or 11gR2 ?
Install the 11gR1
Software
Install the latest
available patchset release (recommended) or the particular patchset to which
you are planning to upgrade the database
Install any required
patches and CPU (Critical Patch Updates)
Upgrade the database
with any of the convenient methods
NOTE : The latest patchset release and critical patch update for
Oracle Database 11gR1 or 11gR2 should be installed prior to upgrading your
databases.
27. How can you
determine the upgrade path to Oracle Database 11gR1 or 11gR2?
The path that you must
take to upgrade to Oracle Database 11g Release 1 (11.1) depends on the release
number of your current database.It might not be possible to upgrade directly
from your current version of Oracle Database to the latest version. Depending
on your current release, you might be required to upgrade through one or more
intermediate releases to upgrade to Oracle Database 11g Release 1 (11.1).
28. Where can you
find the DBUA logs when upgrading to 11gR1 or 11gR2?
You can find the logs in
the following location : $ORACLE_HOME/cfgtoollogs/dbua/$ORACLE_SID
29. Does 32-bit
Oracle Database automatically get converted to 64-bit during upgrade to
64-bit11gR1
or 11gR2?
Yes, the database is
automatically converted to 64-bit during a major release upgrade to Oracle
Database 11gR1 or 11gR2.
NOTE : You will likely
need to increase database initialization parameters affecting the system global
area, such as sga_target and shared_pool_size, to support 64-bit
operation.
30. Is the database
word size changed during the upgrade?
If you are installing
64-bit Oracle Database software but were previously using a 32-bit Oracle
Database installation, then the database will automatically be converted to
64-bit during the upgrade (except when upgrading from release 10.1 to release
10.2).
No comments:
Post a Comment