--grant select on v$session to user;
--grant select on v$lock to user;
my_cursor number;
my_statement varchar2(100);
result integer;
cursor c1 is
select 'alter system kill session ' || '''' || v.sid || ',' || v.SERIAL# || ''' immediate' command
from v$session v
where v.SID in (select distinct(l.sid) from v$lock l where
l.block = 1 and
l.ctime > itime);
-- select 'alter system kill session ' || '''' || to_char(a.sid) || ',' ||
-- to_char(a.serial#) || ''''
-- from v$session a, v$lock b
-- where a.sid = b.sid
-- and b.lmode = 6
-- and a.username like 'THE_BOREING_USER'
-- and b.ctime > time;
begin
open c1;
loop
fetch c1
into my_statement;
exit when c1%notfound;
dbms_output.put_line(my_statement);
my_cursor := dbms_sql.open_cursor;
dbms_sql.parse(my_cursor, my_statement, dbms_sql.v7);
--dbms_output.put_line('1 - ' || my_statement);
result := dbms_sql.execute(my_cursor);
---- execute immediate sqltxt;
dbms_sql.close_cursor(my_cursor);
end loop;
close c1;
result := 0;
end;
/
where o.status <> 'VALID';
begin
--1分钟检查一次
DBMS_JOB.SUBMIT(:JOBNO, 'KILL_LOCKED_USER(90);',SYSDATE,'SYSDATE+(60/86400)',NULL);
COMMIT;
end;
dbms_job.remove('2');
end;
SQL> exec dbms_job.broken(2,true)
SQL> exec dbms_job.broken(2,false)
Wednesday, September 8, 2010
oracle: auto kill lock session
Tuesday, January 22, 2008
How do I see who is currently connected?
SELECT username, program FROM v$session WHERE username IS NOT NULL;
How to see the database version?
To view components and their versions, loaded into the database:
SELECT comp_name, status, version FROM dba_registry;
To view version numbers of core library components, including the database:
SELECT banner FROM v$version;
To display options that are installed (the value column is ‘FALSE’ if the option is not installed)
SELECT parameter, value FROM v$option;
Thursday, January 17, 2008
Hidden columns
Interestingly, dbms_stats will collect statistics on hidden columns, eg, from a function based index, but it doesn't appear that you can actually retrieve them
SQL> create table T ( x number ) ;
Table created.
SQL> create index TX on T ( x+10 );
Index created.
SQL> select column_name from dba_tab_cols
2 where table_name = 'T'
3 and owner = user;
COLUMN_NAME
------------------------------
SYS_NC00002$
X
SQL> declare
2 srec dbms_stats.statrec;
3 DISTCNT number;
4 DENSITY number;
5 NULLCNT number;
6 AVGCLEN number;
7 begin
8 dbms_stats.GET_COLUMN_STATS
9 (OWNNAME=>user
10 ,TABNAME=>'T'
11 ,COLNAME=>'SYS_NC00002$'
12 ,DISTCNT=>distcnt
13 ,DENSITY=>density
14 ,NULLCNT=>nullcnt
15 ,SREC=>srec
16 ,AVGCLEN=>avgclen);
17 end;
18 /
declare
*
ERROR at line 1:
ORA-20000: Unable to get values for column SYS_NC00002$
ORA-06512: at "SYS.DBMS_STATS", line 3976
ORA-06512: at "SYS.DBMS_STATS", line 3991
ORA-06512: at line 8
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select column_name , num_distinct
2 from dba_tab_cols
3 where table_name = 'T'
4 and owner = user;
COLUMN_NAME NUM_DISTINCT
------------------------------ ------------
SYS_NC00002$ 0
X 0
Monday, January 14, 2008
ALTER DATABASE BEGIN BACKUP
Today I have learned a new thing about backup and recovery on how to keep the entire database in backup mode, instead of issuing separate BEGIN BACKUP statement for every tablespace.
I know Oracle strongly recommend of using RMAN for backup and recovery . I thought, this would be good for the DBAs who still use the legacy method of backup, i.e. ALTER TABLESPACE BEGIN/END BACKUP and if they are not aware of this new command in 9i and 10g versions.
Starting with version 9i(I dont know the exact release), Oracle gives the facility to put the entire database in backup by simply using the following command:
ALTER DATABASE BEGIN BACKUP;
ALTER DATABASE END BACKUP; -- to exit from the backup mode.
In version 9i, the above statement can be used only when the database is mounted, not opend. In 10g, this behavior changes. The statement can be executed while the database is open.
-- The following has done with Oracle 10gR1
SYS OCM AS SYSDBA>alter database begin backup;
Database altered.
SYS OCM AS SYSDBA>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 ACTIVE
2 ACTIVE
3 ACTIVE
4 ACTIVE
-- All the datafiles are in backup mode now
SYS OCM AS SYSDBA>alter database end backup;
Database altered.
SYS OCM AS SYSDBA>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 NOT ACTIVE
2 NOT ACTIVE
3 NOT ACTIVE
4 NOT ACTIVE
-- All the datafiles are out of backup mode now.
Happy reading,
Thursday, January 10, 2008
Installing Oracle Applications E-Business 11i on Windows XP / Windowws 2003 Server
Hi! you will be able to install EBS 11i on Windows XP/ Windows 2003 Server, if you just follow these instructions else you should not get into it.
System Requirements Hardware used.
Windows XP (professional or Home) Service Pack 2 with 1 GB RAM ( you can do it on 512 MB also but not at all efficient) and make sure you have NTFS file system.
HD min 150 GB (c’mon your system and staging also need some space don’t agree with who says it work 100 GB with vision DB). You can use external hard drive too.
Don’t listen to those who advise you to install EBS 11i without vision database. I know
you are installing all this for learning etc.
Very Important.
Make sure there is no oracle product installed on your system. No entry of oracle should be in windows registry except system default by windows itself. So it is recommended to use fresh system for this purpose. If you tried a failed installation before on your PC then reinstall the OS (Win XP) again otherwise you will waste your time.
There should be !!!!NO!!! JDK installed on your machine. EBS 11i will install JDK version on your machine otherwise you will get errors I don’t want to discuss all this here. Just NOOOO JDK on your machine.
Pre-requisite software requirements.
1. Microsoft VC++ - http://msdn.microsoft.com/vstudio/express/visualc/download/
while installing VC++ make sure it is done in C:\V98. No spaces in any directory
structure of any software you install as pre-requisite for Oracle Apps. Opt for Register
Environment Variables while installing VC++.
If you installed MS Visual Studio C++ 6.0 then make sure you installed service pack 6.
http://msdn2.microsoft.com/en-us/vstudio/aa718364.aspx
Copy LINK.exe from C:\V98\bin to C:\WINDOWS\System32
2. Perl - http://www.activestate.com/Products/ActivePerl/
Version 5.8 or more and use MSI package type.
Perl is only needed if you are using Oracle Media Pack. If you are downloading from oracle site then you can skip this installation.
2. CYGWIN - http://cygwin.com/
- Download setup.exe to your machine.
Run setup.exe and click Next, which will bring up the Choose Installation Type screen.
Select -> Install from Internet and Click Next
Select -> Root Directory C:\cygwin and select radio button
Install for -> All Users and Default Text File Type -> DOS / text -> Next Local Package Directory can be any but C:\cygwin is recommended. -> Next Direct Connection.
Click on Next again to get to the Select Packages screen, and select the following packages (click on to toggle):
a. All Default
b. Archive Default, plus manually select the zip package
c. Base Default, plus manually select the following extra packages: ash, coreutils,
diffutils, findutils, gawk, grep, sed, tar and which
d. Devel Default, plus manually select binutils, gcc, gcc-core, gcc-g++, make and
mktemp
e. Doc Default, plus manually select cygwin-doc and man
f. Editors Default, plus manually select vim
g. Interpreters Default, plus manually select gawk
h. Shells Default, plus manually select ash and tcsh
i. Utils Default, plus manually select cygutils and file
Click on Next again to download the selected files. It will take some time depending upon mirror site you selected and after download cygwin setup will be automatically starts.
After installation completed
Open folder C:\cygwin\bin
And rename followings
gawk.exe to awk.exe
grep.exe to egrep.exe
make.exe to gnumake.exe
gcc.exe to cc.exe
You can override the name if system says that awk or egrep or cc already exits just remove or move to some other place.
Set the Path to include the C:\cygwin\bin
C:\> set PATH = %PATH%;c:\cygwin\bin
And then check your PATH to see that it include C:\cygwin\bin and C:\V98\bin
C:\>echo %PATH%
Oracle Media
You should be having Lasted available Oracle media pack (recommended) or Software download from oracle site and follow the unzipping instruction as per oracle documentation.
Domain Name Configuration
1. Open file c:\windows\system32\drivers\etc\hosts. Enter a domain next to localhost entry you will be using this domain while installing Oracle Applications. I used mydomain so you can replace what ever you want. 198.168.1.102 is IP Address of my PC you should enter your machine’s IP. If you don’t know it you can find it.
C:\>ipconfig
Example
127.0.0.1 localhost mydomain mydomain.com
198.168.1.102 mydomain mydomain.com
2. Go to Control Panel -> Systems -> Computer Name Tab -> Click on Change -> Click on More -> Enter Primary DNS Suffix as ‘mydomain’.
3. Open Network connections, Select your Connection on which your machine is connected (You will find Wireless or LAN) and right click and select properties. Select Internet Protocol( TCP/IP) and click on to properties. Click on Advanced button and go to DNS tab. Enter DNS suffix for this connection as ‘mydomain’.
4. Restart the PC.
.Staging the Oracle
If you have Oracle Media Pack then
Insert the Start Here DVD in DVD Rom and go to folder
Stage11i\startCD\Disk1\rapidwiz in DOS Prompt and type
D:\Stage11i\startCD\Disk1\rapidwiz\perl adautostg.pl
And follow the wizard.
Recommended Steps but not necessary.
There steps are only if your installation fails due to any reason so you can restore your registry and system to pre installation state.
When Staging is done successfully then export the registry and save it on a file.
Start -> Run -> regedit-> File -> Export and select the location where you want to store it .
Take a Computer Restore Point.
Start -> All Programs -> Accessories -> System Tools -> System Restore ->Create a Restore Point -> Enter name of restore point for example ‘PreOra’ and Press the Create.
Restart the PC.
Installing the the EBS 11i
So far you have done a great job. Now time to get the job done.
Go to folder C:\Stage11i\startCD\Disk1\rapidwiz or where you have done your staging on hard drive and just click RapidWiz
On welcome screen -> Next and Select
Install Oracle Applications E-Bussiness 11i Check on Use Express Configuration -> Next
In Express Configuration Information screen enter the following:
Database Type = Vision Demo Database
Database SID = VIS
MKS directory = C:\cygwin\bin or where “cygwin\bin” is
MSDEV directory = C:\V98 or where VC++ was installed
Domain = mydomain or what so ever is your domain.
Oracle Base directory =C:\oracle or where ever you want to install oracle
applications
Follow the wizard and you should pass all the Pre-Install Checks
If not then you did not follow instructions above and help yourself.
If you passed all the checks then just Click Next and follow the Wizard and installation will be done in 5 steps and take about 2-3 hrs depends upon the system.
After the installation is completed successfully (it should be) then Post-Installation Checks will be preformed. If you passed all the checks It is really a Wonderful and You Rocks
If your system don’t pass the Post-Installation Checks even then don’t worry just smile coz installation is successful you need to restart your PC and Open Command Prompt open to staging directory c:\Stage11i\startCD\Disk1\rapidwiz
And now just type rapidwiz –restart
Follow the Wizard and Enter all the info as you enter before Now This time it will take few minutes and you should pass Post-Installation Checks
If still a problem just repeat above step again you will get to it . Restart the PC and enter the URL in web browser
http://hostname.mydomain:8000/
E-Bussiness Login Username = sysadmin Password = sysadmin
These are the successful installation steps. I practically have done all that. Thanks to Mr. Naveed
Cheers …..
Installation / Re-Installation of Oracle Database 10g on Windows
1. Take back-up of data (Full Database).
2. Shutdown your database. (Shutdown Abort).
3. Take back-up of SP file.
(If you have set some parameters previously according to your environment)
4. Note the size and names of all table spaces.
5. Delete Database through wizard (If previously installed).
6. Run Installer and uninstall Oracle from your system.
7. Remove Oracle entries from registry.
8. Restart your system.
9. Remove Oracle Directory from System.
10. Check services from Administrative tools. Please verify that there should not any Oracle related service existing in the list. No TNS, no oracle home etc.
11. Reboot your system.
12. Run Oracle Installer and start installation. Follow wizard and select options according to your requirements.
13. When Database is created, verify your work by connecting to database.
14. Shutdown Database.
15. Restore SP file.
16. Start Database.
(Note if you find any error on startup, then you have to generate SP file from init.ora file)
17. Install patch from its installer and run the related scripts. (It will take around 45 minutes so no need to worry).
18. Reboot your machine.
19. Create table spaces by same name and size as we noted in step 4.
20. Load (Import) backup from dmp file. Create log file as well.
(View log file. If you find any error of “table space already exists”, then don’t worry. It is because we have created the table spaces our selves.)
Good Luck!!!
Wednesday, January 9, 2008
Inconsistency between DBA_DATA_FILES, DBA_FREE_SPACE, and DBA_SEGMENTS views
A question has been raised on Technet forum that inconsistent information is being returned from Oracle dictionary views. OP is trying to relate used/free bytes returned by these dictionary views.
Information provided by OP:
Total bytes occupied by data files is: 11,010,048,000
Total bytes available are: 220,200,960
Total bytes used by objects: 10,989,076,480
Free Space + Used Space = 220,200,960 + 10,989,076,480 = 11,209,277,440
So, the sum of “Free Space” and “Used Space” is more than the “Total Space” available to the datafiles. The question is “How can I get more space than the existing one?”
Answer to the question goes here:
SQL> conn test/test
Connected.
SQL>
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMPDATA TABLE
TEST_ERRORS TABLE
SQL> create table test1 as select * from tab;
Table created.
SQL> create table test2 as select * from tab;
Table created.
SQL> create table test3 as select * from tab;
Table created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST1 TABLE
TEST2 TABLE
TEST3 TABLE
EMPDATA TABLE
TEST_ERRORS TABLE
Now, I will drop these newly created tables:
SQL> drop table test1;
Table dropped.
SQL> drop table test2;
Table dropped.
SQL> drop table test3;
Table dropped.
Now, query the dictionary views for used, free and allocated bytes/blocks information:
SQL> select blocks from dba_data_files where tablespace_name = 'TEST_TS';
BLOCKS
----------
3712
SQL> select sum(blocks) from dba_free_space where tablespace_name = 'TEST_TS';
SUM(BLOCKS)
-----------
3680
SQL> select sum(blocks) from dba_segments where tablespace_name = 'TEST_TS';
SUM(BLOCKS)
-----------
48
Opps, we get more space (3680 + 48 = 3728) than being allocated to the datafiles. Probably, by now you might have arrived to the answer, but let me reveal it to you.
OP hasn’t mentioned the Oracle database version but I am pretty sure it’s Oracle 10g or above. With Oracle database 10g, Oracle has added a new feature of recycle bin. When you drop an object it goes and stays in your recycle bin and will occupy the same amount of space. You need to purge the object to reclaim the space.
This is what is happening in this case. Dropped objects are still lying in the TEST_TS tablespace and being counted by the DBA_SEGMENTS view, whereas, DBA_FREE_SPACE correctly report the free space available.
Let me purge the dropped objects out of the recycle bin and rerun the queries:
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST1 BIN$BEExbY8xS0aXH3U+e9XPDg==$0 TABLE 2007-12-18:13:42:36
TEST2 BIN$WGV0P4B4TaCUukiPyctTPg==$0 TABLE 2007-12-18:13:42:38
TEST3 BIN$1P4aTA1IR8ijw4btdRkmzw==$0 TABLE 2007-12-18:13:42:39
SQL> purge recyclebin;
Recyclebin purged.
SQL> select blocks from dba_data_files where tablespace_name = 'TEST_TS';
BLOCKS
----------
3712
SQL> select sum(blocks) from dba_free_space where tablespace_name = 'TEST_TS';
SUM(BLOCKS)
-----------
3680
SQL> select sum(blocks) from dba_segments where tablespace_name = 'TEST_TS';
SUM(BLOCKS)
-----------
24
Wow, Oracle now reports correctly. The sum of “Free Space” and “Used Space” (3680 + 24 = 3704) is less than the “Total Space” (3712) available to the datafiles.
Tuesday, January 1, 2008
alter database add logfile size 1e7;
I am in the processing in adding logfiles to a 10gR2 database.
SQL> alter database add logfile group 10 size 1e7;
Database altered.
$ ls -l
-rw-r----- 1 oracle 10000896 Nov 1 15:00
o1_mf_10_3lmq05ld_.log
The file size is 10,000,896 bytes.
What about this :
SQL> alter database drop logfile group 10;
Database altered.
SQL> alter database add logfile size 1e;
alter database add logfile size 1e
*
ERROR at line 1:
ORA-00741: logfile size of (2251799813685248) blocks
exceeds maximum logfile size
No way! Oracle does not want me to add a logfile of 1 Exabyte !
Remember the logfile blocks are OS blocks of 512 bytes. Not Oracle blocks.
Ok, let’s try something else
SQL> alter database add logfile size 1p;
alter database add logfile size 1p
*
ERROR at line 1:
ORA-00741: logfile size of (2199023255552) blocks
exceeds maximum logfile size
No, one Petabyte is not a realistic size for a log file.
I have one more try, but unfortunately it works :evil:
SQL> alter database add logfile size 1t;
...
It just takes ages…
$ ls -l
-rw-r----- 1 oracle dba 1099511628288 Nov 1 14:49
o1_mf_5_3lmpb6w6_.log
$ du -g o1_mf_5_3lmpb6w6_.log
6.09 o1_mf_5_3lmpb6w6_.log
$ df -gt .
Filesystem GB blocks Used Free %Used Mounted on
/dev/u02_lv 140.00 19.32 120.68 14% /u02
The ls shows the file size has been set to 1T and 6 Gigabytes have been allocated yet. Since I do not want to fill my filesystem, I just shutdown-abort my instance and remove that file…
How to resolve ORA-09925 ?
This morning I had to solve an ORA-09925: Unable to create audit trail file and it was not as straightforward as usual…
There is a note 69642.1 on Metalink, [edit]which is now up to date for 10gR2[/edit].
1) AUDIT_FILE_DEST is not writable
$ env
_=/usr/bin/env
ORACLE_SID=FOO
TERM=dtterm
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_3
PWD=/u01/app/oracle/product/10.2.0/db_3
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL> startup
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 9925
$ grep -i audit_file_dest $ORACLE_HOME/dbs/*$ORACLE_SID.ora
audit_file_dest=/bar
$ ls -lad /bar
/bar not found
$ su -
root's Password:
# mkdir /bar
# exit
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL> startup
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
$ su -
root's Password:
# chown oracle /bar
# exit
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL> startup quiet nomount
ORACLE instance started.
SQL> shutdown abort
ORACLE instance shut down.
2) $ORACLE_BASE/admin/$ORACLE_SID/adump exists and is not writable :!:
$ ls -lad $ORACLE_BASE/admin/$ORACLE_SID/adump
drwxr-xr-x 2 root dba .../admin/FOO/adump
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 -
Production on Mon Dec 17 09:02:29 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
$ su -
root's Password:
# chown oracle /bar/admin/FOO/adump
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 -
Production on Mon Dec 17 09:02:48 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> quit
3) $ORACLE_HOME/rdbms/audit is not writable
$ cat $ORACLE_HOME/dbs/init$ORACLE_SID.ora
db_name=FOO
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 -
Production on Mon Dec 17 08:48:09 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
$ ls -lad $ORACLE_HOME/rdbms/audit
drwxr-x--- 2 root dba ... $ORACLE_HOME/rdbms/audit
$ cd $ORACLE_HOME; su
root's Password:
# chown oracle ./rdbms/audit
# exit
$ $ORACLE_HOME/bin/sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 -
Production on Mon Dec 17 08:49:12 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> quit
CPU2007Apr
I just downloaded and installed the Critical Patch Update April 2007
$ lsnrctl stop
...
The command completed successfully
$ sqlplus / as sysdba
SQL> shutdown immediate
...
ORACLE instance shut down.
SQL> quit
$ cd /tmp/5901891
$ ORACLE_HOME/OPatch/opatch apply
... Finish at Wed Apr 18 10:28:17 CEST 2007
$ lsnrctl start
...
The command completed successfully
$ sqlplus / as sysdba
SQL> startup
...
Database opened.
SQL> @?/cpu/CPUApr2007/catcpu
SQL> @?/rdbms/admin/utlrp
The logfile of opatch is $ORACLE_HOME/cfgtoollogs/opatch/opatchdate.log and the logfile of the catcpu is APPLY_sid_date.log. ORA-02303 can be safely ignored.
SQL> select * from dba_registry_history
ACTION_TIME
------------------------------
ACTION
------------------------------
NAMESPACE
------------------------------
VERSION
------------------------------
ID
----------
COMMENTS
------------------------------
18-APR-07 10.38.09.565465 AM
CPU
SERVER
10.2.0.3.0
5901891
CPUApr2007
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
Successfully applied
Create your database with dbca
With dbca you can fasten the procedure of creating databases in your company. You can also run this in silent mode and create exactly the database you want, with your redo/undo size, with your parameters settings. You create the template once, and use it many times. Fast and easy :-D
I have one database which I created with SQL*PLUS called LSC01.
1) Create template lsc-template
dbca
–> Manage Templates
–> Create a database template
–> from an existing database (structure as well as data)
–> LSC01
–> lsc-template
–> convert the file locations to use OFA structure
This takes some place on disk and will speed up database creation. Technically speaking, it is doing a compressed backup with RMAN that will be restore, and restore is way faster than create database
2) Create database LSC99 in silent mode or progressOnly mode.
dbca -silent -createDatabase -templateName lsc-template.dbc -gdbName LSC99.lcsys.ch
It took me only two minutes to create my database on my notebook !
Try it ! Of course I expect comments on this post
Oracle 11g Hot patching
Online Patching : you can apply or roll back online patches while the RDBMS instance is running
1) download an interim patch for 11g, f.ex. dummy patch 6198642
2) unzip p6198642_111060_LINUX.zip
3) cd 6198642
4) $ORACLE_HOME/OPatch/opatch apply -silent -connectString LSC08 -runSql
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.
Oracle Home : /opt/oracle/product/11/db_4
Central Inventory : /opt/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.1.0.6.0
OUI version : 11.1.0.6.0
OUI location : /opt/oracle/product/11/db_4/oui
Log file location : /opt/oracle/product/11/db_4/cfgtoollogs/
opatch/opatch2007-12-05_21-23-42PM.log
RollbackSession rolling back interim patch ‘6198642′ from OH
‘/opt/oracle/product/11/db_4′
Running prerequisite checks…
OPatch detected non-cluster Oracle Home from the inventory
and will patch the local system only.
Please shutdown Oracle instances running out of this
ORACLE_HOME on the local system.
(Oracle Home = ‘/opt/oracle/product/11/db_4′)
Is the local system ready for patching? [y|n]
Y (auto-answered by -silent)
User Responded with: Y
Backing up files affected by the patch ‘6198642′ for restore.
This might take a while…
Execution of ’sh /opt/oracle/product/11/db_4/.patch_storage/
6198642_May_07_2007_00_50_36/original_patch/custom/
scripts/pre -rollback 6198642 ‘:
Return Code = 0
Patching component oracle.rdbms, 11.1.0.6.0…
Copying file to “/opt/oracle/product/11/db_4/cpu/CPUDummy2007/
catcpu.sql”
RollbackSession removing interim patch ‘6198642′ from inventory
—————————————————————
This is a dummy patch for testing only
—————————————————————
Execution of ’sh /opt/oracle/product/11/db_4/.patch_storage/
6198642_May_07_2007_00_50_36/original_patch/custom/scripts/
post -rollback 6198642 ‘:
Return Code = 0
Running the “apply” sql script “/opt/oracle/product/11/db_4/cpu/
CPUDummy2007/catcpu.sql” with reference to ‘patchmd.xml’ file
for the patch “6198642″…
The local system has been patched and can be restarted.
OPatch succeeded.
OPatch did run the necessary script (catcpu for Dummy2007) on the various instances (LSC08). It needed only one step and 34 seconds on my notebook. This patch is a dummy patch, let’s wait for CPU January to see if it is online applicable ;-)
