The RMAN documentation differentiates between "inconsistent" and "consistent" backups on the basis of whether the database is OPEN or not (respectively) during the Backup.
The nomenclature "inconsistent backup" makes me nervous. Why not call it the good old "HOT" Backup ?
To do a consistent" backup with RMAN, the database must be mounted as RMAN needs to access and update the controlfiles. With an OS scripted backup pre-RMAN, the database was truly "COLD" -- there would be no Oracle processes running.
RMAN does not backup the Online Redo Log files. With an OS scripted backup, you had the option -- you could choose to include these files in your backup if you were careful about how you planned to use Cold Backups for Roll-Forward recoveries with ArchiveLog.
Since RMAN does not backup the Online Redo Log files, you must, perforce, OPEN RESETLOGS on a Restore. With a scripted backup, if you also included your Online Redo Log files in your backup and restore (provided that you did not plan to apply any ArchiveLogs), you could simply STARTUP the database and continue LogSequenceNumbers again. (Of course, you might be duplicating LogSequenceNumbers if the database had been active in ArchiveLog mode since the backup, so you have to be careful to distinguish the two "streams" of ArchiveLogs).
Here below is the simplest "consistent" Backup and Restore using RMAN :
C:\>rman
Recovery Manager: Release 10.2.0.3.0 - Production on Thu May 8 22:20:17 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database (not started)
RMAN> startup mount
Oracle instance started
database mounted
Total System Global Area 536870912 bytes
Fixed Size 1291652 bytes
Variable Size 297798268 bytes
Database Buffers 234881024 bytes
Redo Buffers 2899968 bytes
RMAN> backup database;
Starting backup at 08-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=58 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\NITISH10G\SYSTEM01.DBF
input datafile fno=00003 name=C:\NITISH10G\SYSAUX01.DBF
input datafile fno=00002 name=F:\NITISH10G\TEST_TBS_01.DBF
input datafile fno=00004 name=C:\NITISH10G\USERS01.DBF
input datafile fno=00005 name=C:\NITISH10G\EXAMPLE01.DBF
input datafile fno=00007 name=C:\NITISH10G\UNDO.DBF
channel ORA_DISK_1: starting piece 1 at 08-MAY-08
channel ORA_DISK_1: finished piece 1 at 08-MAY-08
piece handle=C:\NITISH10G_FLASH\NITISH10G\BACKUPSET\2011_05_08\O1_MF_NNNDF_TAG200
0508T222041_42631X6C_.BKP tag=TAG20110508T222041 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:47
Finished backup at 08-MAY-08
Starting Control File and SPFILE Autobackup at 08-MAY-08
piece handle=C:\NITISH10G_FLASH\NITISH10G\AUTOBACKUP\2011_05_08\O1_MF_S_654214767
426358M1_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 08-MAY-08
RMAN>
RMAN> shutdown
database dismounted
Oracle instance shut down
RMAN>
******************* BACKUP COMPLETED *****************
******************************************************
========= database files deleted ====================
++++++++++++++++++++++++++++++++++++++++++++++++++++++
======================================================
C:\>rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Thu May 8 22:32:07 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 536870912 bytes
Fixed Size 1291652 bytes
Variable Size 301992572 bytes
Database Buffers 230686720 bytes
Redo Buffers 2899968 bytes
RMAN>
RMAN> restore controlfile from autobackup;
Starting restore at 08-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=57 devtype=DISK
recovery area destination: \NITISH10G_FLASH
database name (or database unique name) used for search: NITISH10G
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: C:\NITISH10G_FLASH\NITISH10G\AUTOBACKUP\2011
_05_08\O1_MF_S_654214767_426358M1_.BKP
channel ORA_DISK_1: control file restore from autobackup complete
output filename=C:\NITISH10G\CONTROL01.CTLa
output filename=C:\NITISH10G\CONTROL02.CTL
output filename=C:\NITISH10G\CONTROL03.CTL
Finished restore at 08-MAY-08
RMAN>
RMAN> restore database;
Starting restore at 08-MAY-08
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/08/2011 22:33:28
ORA-01507: database not mounted
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN>
RMAN> restore database;
Starting restore at 08-MAY-08
Starting implicit crosscheck backup at 08-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=57 devtype=DISK
Crosschecked 45 objects
Finished implicit crosscheck backup at 08-MAY-08
Starting implicit crosscheck copy at 08-MAY-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 08-MAY-08
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: C:\NITISH10G_FLASH\NITISH10G\AUTOBACKUP\2011_05_08\O1_MF_S_654214767_42
6358M1_.BKP
using channel ORA_DISK_1
skipping datafile 2; already restored to file F:\NITISH10G\TEST_TBS_01.DBF
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\NITISH10G\SYSTEM01.DBF
restoring datafile 00003 to C:\NITISH10G\SYSAUX01.DBF
restoring datafile 00004 to C:\NITISH10G\USERS01.DBF
restoring datafile 00005 to C:\NITISH10G\EXAMPLE01.DBF
restoring datafile 00007 to C:\NITISH10G\UNDO.DBF
channel ORA_DISK_1: reading from backup piece C:\NITISH10G_FLASH\NITISH10G\BACKUPS
ET\2011_05_08\O1_MF_NNNDF_TAG20110508T222041_42631X6C_.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\NITISH10G_FLASH\NITISH10G\BACKUPSET\2011_05_08\O1_MF_NNNDF_TAG2011
0508T222041_42631X6C_.BKP tag=TAG20110508T222041
channel ORA_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 08-MAY-08
RMAN>
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 05/08/2011 22:36:48
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN>
RMAN> alter database open resetlogs;
database opened
RMAN>
Sunday, 29 January 2012
Friday, 27 January 2012
Upgrade Oracle to 11.1.0.7
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;
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;
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
Subscribe to:
Posts (Atom)