Monday, November 7, 2011

RMAN duplicate in details


(originally published at Pythian)
Its all started from the question on communities.oracle.com
An "enhancement" in 11.2 prevents RMAN duplicating non self-contained subset of a database
where new feature of RMAN in 11.2 was shown which prevents duplication of sets of tablespaces if they are not self-contained.

I took debug of duplication and figured out that RMAN internally executes procedure dbms_tts.transport_set_check
preventing any further copy operations if tablespaces do not satisfy TTS requirements.

Now the plot thickens and considering RMAN shows everything that it does in log file I decided to repeat the same steps
but one by one and manually.

First of all I took log of successful duplication for the database but only for one
self-contained tablespace 'T'

rman target sys/pwd@//oel5/d122 auxiliary sys/...@temp \
cmdfile=duplicate_wts.cmd debug trace=wts.out
Recovery Manager: Release 11.2.0.2.0 - Production on ...
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
RMAN-06005: connected to target database: D122 (DBID=3977667195)
RMAN-06549: connected to auxiliary database: TEMP (not mounted)
run {
duplicate target database to temp from active database
tablespace 'T'
spfile
set db_file_name_convert '/u02/oradata/d122', '/u02/oradata/temp'
set log_file_name_convert '/u02/oradata/d122', '/u02/oradata/temp'
set sga_target '600M';
}
exit


It produced the output of commands executed by RMAN internally
and went successfully opening database with resetlogs but failed
on the step of dropping not copied tablespaces since they were not self-contained.
RMAN-06958: Executing: drop tablespace "P5" including contents cascade constraints
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at ...
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: 
ORA-14404: partitioned table contains partitions in a different tablespace Recovery Manager complete.


But I got the code that was performed by RMAN during duplication
and generally those blocks were headed by message
"RMAN-08161: contents of Memory Script:"
although had to add "CREATE CONTROLFILE" block as well:
{
backup as copy reuse
targetfile  '/u02/app/oracle/product/11.2.0/dbhome_1/dbs/spfiled122.ora' auxiliary format
'/u02/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletemp.ora'   ;
sql clone "alter system set spfile= ''/u02/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletemp.ora''";
}
...
{
sql clone "alter system set  db_name =
''TEMP'' comment=
''duplicate'' scope=spfile";
sql clone "alter system set  db_file_name_convert =
''/u02/oradata/d122'', ''/u02/oradata/temp'' comment=
'''' scope=spfile";
sql clone "alter system set  log_file_name_convert =
''/u02/oradata/d122'', ''/u02/oradata/temp'' comment=
'''' scope=spfile";
sql clone "alter system set  sga_target =
600M comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
...
{
sql clone "alter system set  db_name =
''D122'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set  db_unique_name =
''TEMP'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format  '/u02/oradata/d122/control01.ctl';
restore clone controlfile to  '/u02/app/oracle/fast_recovery_area/d122/control02.ctl' from
'/u02/oradata/d122/control01.ctl';
alter clone database mount;
}
...
{
sql clone 'alter database flashback off';
set newname for datafile  1 to
"/u02/oradata/temp/system01.dbf";
set newname for datafile  2 to
"/u02/oradata/temp/sysaux01.dbf";
set newname for datafile  3 to
"/u02/oradata/temp/undotbs01.dbf";
set newname for datafile  8 to
"/u02/oradata/temp/t.dbf";
backup as copy reuse
datafile  1 auxiliary format
"/u02/oradata/temp/system01.dbf"   datafile
2 auxiliary format
"/u02/oradata/temp/sysaux01.dbf"   datafile
3 auxiliary format
"/u02/oradata/temp/undotbs01.dbf"   datafile
8 auxiliary format
"/u02/oradata/temp/t.dbf"   ;
sql 'alter system archive log current';
}
...
{
backup as copy reuse
archivelog like  
"/u02/app/oracle/fast_recovery_area/D122/archivelog/2011_10_19/o1_mf_1_401_79wsdj3m_.arc" 
auxiliary format 
"/u02/app/oracle/fast_recovery_area/TEMP/archivelog/2011_10_19/o1_mf_1_401_%u_.arc"   ;
catalog clone recovery area;
switch clone datafile all;
}
...
{
set until scn  11283882;
recover
clone database
skip forever tablespace  "USERS",
"P5",
"P4",
"P3",
"P2",
"P1"    delete archivelog
;
}
...
{
sql clone "alter system set  db_name =
''TEMP'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset  db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
...
RMAN-06162: sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEMP" RESETLOGS ARCHIVELOG
MAXLOGFILES     16
MAXLOGMEMBERS      3
MAXDATAFILES      100
MAXINSTANCES     8
MAXLOGHISTORY      292
LOGFILE
GROUP  1 ( '/u02/oradata/temp/redo01.log' ) SIZE 50 M  REUSE,
GROUP  2 ( '/u02/oradata/temp/redo02.log' ) SIZE 50 M  REUSE,
GROUP  3 ( '/u02/oradata/temp/redo03.log' ) SIZE 50 M  REUSE
DATAFILE
'/u02/oradata/temp/system01.dbf'
CHARACTER SET WE8MSWIN1252
...
{
set newname for tempfile  1 to
"/u02/oradata/temp/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy  "/u02/oradata/temp/sysaux01.dbf",
"/u02/oradata/temp/undotbs01.dbf",
"/u02/oradata/temp/t.dbf";
switch clone datafile all;
}
...
{
Alter clone database open resetlogs;
}


Based on the code generated by RMAN I created my own block
but had to divide it on several portions where first part
included all actions before copy of archive logs to auxiliary site
because of simple reason - I was not able to provide max SCN in latest sequence
(which will be generated by "sql 'alter system archive log current';")
at the time of script start and there is no such variable as current latest
sequence or SCN number:
run {
backup as copy reuse
targetfile  '/u02/app/oracle/product/11.2.0/dbhome_1/dbs/spfiled122.ora' auxiliary format
'/u02/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletemp.ora'   ;
sql clone "alter system set spfile= ''/u02/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletemp.ora''";
sql clone "alter system set  db_name = ''TEMP'' comment= ''duplicate'' scope=spfile";
sql clone "alter system set  db_file_name_convert =
''/u02/oradata/d122'', ''/u02/oradata/temp'' comment= '''' scope=spfile";
sql clone "alter system set  log_file_name_convert =
''/u02/oradata/d122'', ''/u02/oradata/temp'' comment= '''' scope=spfile";
sql clone "alter system set  sga_target = 600M comment= '''' scope=spfile";

shutdown clone immediate;
startup clone nomount;

sql clone "alter system set  db_name =''D122'' comment=''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set  db_unique_name =
''TEMP'' comment=''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;
startup clone force nomount

backup as copy current controlfile auxiliary format  '/u02/oradata/d122/control01.ctl';
restore clone controlfile to  '/u02/app/oracle/fast_recovery_area/d122/control02.ctl' from
'/u02/oradata/d122/control01.ctl';

alter clone database mount;
sql clone 'alter database flashback off';

set newname for datafile  1 to "/u02/oradata/temp/system01.dbf";
set newname for datafile  2 to "/u02/oradata/temp/sysaux01.dbf";
set newname for datafile  3 to "/u02/oradata/temp/undotbs01.dbf";
set newname for datafile  8 to "/u02/oradata/temp/t.dbf";

backup as copy reuse
datafile 1 auxiliary format "/u02/oradata/temp/system01.dbf"
datafile 2 auxiliary format "/u02/oradata/temp/sysaux01.dbf"
datafile 3 auxiliary format "/u02/oradata/temp/undotbs01.dbf"
datafile 8 auxiliary format "/u02/oradata/temp/t.dbf";

sql 'alter system archive log current';
}


The code ran without problems, I took latest SCN and sequence number
and tried to copy them to auxiliary side but got error due to non-existed
directory for archive logs and had to create it manually.

Going forward, I was able to catalog data files copies and switched to them
with further successful recovery. However, everything of that was done
at auxiliary side with connection to it as "target".
Trying to do it with clone clause from primary side I was not able to catalog
copies and recovery failed restoring data files.

Skipping that not clear part yet, I proceed further and changed the name
from D122 to TEMP in spfile although could not find one of "shutdown"
operations being presented in printed "memory scripts".

And here started the part which I could not understand for a long time -
creation of controlfile with system data file and catalog operation of rest
of data files which were copied. It was my understanding that control file
should be created with all required data files and there is no other way to
"catalog" datafile copies in control file. But following RMAN "recipe" I took contents of
the code, recreated control file and cataloged datafiles, but switch operation failed:
CREATE CONTROLFILE REUSE SET DATABASE "TEMP" RESETLOGS ARCHIVELOG
MAXLOGFILES     16
MAXLOGMEMBERS      3
MAXDATAFILES      100
MAXINSTANCES     8
MAXLOGHISTORY      292
LOGFILE
GROUP  1 ( '/u02/oradata/temp/redo01.log' ) SIZE 50 M  REUSE,
GROUP  2 ( '/u02/oradata/temp/redo02.log' ) SIZE 50 M  REUSE,
GROUP  3 ( '/u02/oradata/temp/redo03.log' ) SIZE 50 M  REUSE
DATAFILE
'/u02/oradata/temp/system01.dbf'
CHARACTER SET WE8MSWIN1252
/
Control file created.

RMAN> run {
set newname for tempfile  1 to
"/u02/oradata/temp/temp01.dbf";
switch tempfile all;
catalog datafilecopy "/u02/oradata/temp/sysaux01.dbf",
"/u02/oradata/temp/undotbs01.dbf",
"/u02/oradata/temp/t.dbf";
switch datafile all;
}

executing command: SET NEWNAME
using target database control file instead of recovery catalog
cataloged datafile copy
datafile copy file name=/u02/oradata/temp/sysaux01.dbf RECID=1 STAMP=765296913
cataloged datafile copy
datafile copy file name=/u02/oradata/temp/undotbs01.dbf RECID=2 STAMP=765296913
cataloged datafile copy
datafile copy file name=/u02/oradata/temp/t.dbf RECID=3 STAMP=765296913

RMAN>
RMAN> list datafilecopy all;
List of Datafile Copies
=======================
Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
1       2    A 23-OCT-11       11673680   23-OCT-11
Name: /u02/oradata/temp/sysaux01.dbf
Tag: TAG20111023T135321
2       3    A 23-OCT-11       11673680   23-OCT-11
Name: /u02/oradata/temp/undotbs01.dbf
Tag: TAG20111023T135321
3       8    A 23-OCT-11       11673680   23-OCT-11
Name: /u02/oradata/temp/t.dbf
Tag: TAG20111023T135321

RMAN> switch datafile 2,3,8 to copy;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 10/23/2011 14:29:20
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: 2


I checked trace file of RMAN duplicate trying to find any hidden commands,
checked notes about duplicate on MOS, even created SR for it but
did not get the answer how RMAN does it.

And finally command "set newname for tempfile ..." revealed the secret -
set newname should be executed in the same block where datafile copies
are cataloged! As simple as that!

I set names for copies, "cataloged" them, opened database with resetlogs
and got the same picture for other files in v$datafile as when RMAN did duplication
run {
set newname for datafile 2 to "/u02/oradata/temp/sysaux01.dbf";
set newname for datafile 3 to "/u02/oradata/temp/undotbs01.dbf";
set newname for datafile 8 to "/u02/oradata/temp/t.dbf";
catalog clone datafilecopy  "/u02/oradata/temp/sysaux01.dbf", 
"/u02/oradata/temp/undotbs01.dbf", "/u02/oradata/temp/t.dbf";
switch clone datafile all;
}

select name, file#, status from v$datafile;
NAME
-------------------------------------------------
     FILE# STATUS
---------- -------
/u02/oradata/temp/system01.dbf
         1 SYSTEM
/u02/oradata/temp/sysaux01.dbf
         2 RECOVER
/u02/oradata/temp/undotbs01.dbf
         3 RECOVER
/u02/oradata/temp/t.dbf
         8 RECOVER

run {
   Alter clone database open resetlogs;
}

NAME
---------------------------------------------------------
     FILE# STATUS
---------- -------
/u02/oradata/temp/system01.dbf
         1 SYSTEM
/u02/oradata/temp/sysaux01.dbf
         2 ONLINE
/u02/oradata/temp/undotbs01.dbf
         3 ONLINE
/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00004
         4 RECOVER
/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00005
         5 RECOVER
/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00006
         6 RECOVER
/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00007
         7 RECOVER
/u02/oradata/temp/t.dbf
         8 ONLINE
/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00009
         9 RECOVER
/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00010
        10 RECOVER
/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00011
        11 RECOVER
/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00012
        12 RECOVER
/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00013
        13 RECOVER


Have a good day!

Wednesday, October 12, 2011

Dear DBA_SCHEDULER_RUNNING_JOBS, where have you been?!

originally published at Pythian
(story based on real events)
Where: Meeting room, Pythian office, Sydney, Australia.
When: Afternoon, after lunch time.
What: Discussion of planned maintenance for one of clients.

DBA1: We should check DBA_JOBS_RUNNING and DBA_SCHEDULER_RUNNING_JOBS
that there are no running jobs. And if there are no any we should continue with next step of our plan.

DBA2: Do you mean V$SCHEDULER_RUNNING_JOBS instead of DBA_SCHEDULER_RUNNING_JOBS?

DBA1: No, why?

DBA2: Because there is no such view in dictionary.

DBA1: But it is there in database. (He connects to sqlplus and issues
select count(*) from DBA_SCHEDULER_RUNNING_JOBS. It immediately returns 0 rows)

DBA2: (His eyes are ready to jump out of eye sockets. He almost pulls out laptop from DBA1 hands
and murming starts to type commands in sqlplus) ...How that can be... ...not in dict... not in dictionary...
...it sent word...(He typed select table_name from dict where table_name like 'DBA_SCHEDULER%')
You see, it is not in a dictionary although there are others and I thought it does not exist, until now...
(pause)
Excuse me my dear friend (DBA2 appealing to DBA1) how about we continue discussion later
since there should be a reason of such behaviour and I have to reveal it...
(He runs away from the room to find the reason of
DBA_SCHEDULER_RUNNING_JOBS disapperance from dictionary view)
(scene finished)

That happened today and I am being DBA2 started to check what's the secret hides the view.
I checked documentation - description of the view is there, I checked MOS - search did not show anything
related, nothing was found through Internet search either. So I decided to dig into dictionary view
and using select dbms_metadata.get_ddl('VIEW', 'DICTIONARY') from dual got the first part of the query
where sys.obj$ and sys.com$ tables were joined.

CREATE OR REPLACE FORCE VIEW "SYS"."DICTIONARY" ("TABLE_NAME", "COMMENTS") AS
select o.name, c.comment$
from sys.obj$ o, sys.com$ c
where o.obj# = c.obj#(+)
and c.col# is null
and o.owner# = 0
and o.type# = 4
and (o.name like 'USER%'
or o.name like 'ALL%'
or (o.name like 'DBA%'
and exists
(select null
from sys.v$enabledprivs
where priv_number = -47 /* SELECT ANY TABLE */)
)
)
union all
...


DBA_SCHEDULER_RUNNING_JOBS was not shown there, but there were other DBA_SCHEDULER... views
and investigating further I found the reason why the view is not shown in dict view
(which is synonym on dictionary).
Simply because there is no entry with col# equals to NULL for the object DBA_SCHEDULER_RUNNING_JOBS.
NULL value in col# is just identification of a comment for a table and obviously that comment was missed
for our loss.

SQL> select col#, obj#, comment$ from com$ where obj# = (select obj# from obj$
where name = 'DBA_JOBS_RUNNING' and owner# = 0) and col# is null;

COL# OBJ# COMMENT$
---------- ---------- ------------------------------------------------------------------------------------------
4828 All jobs in the database which are currently running, join v$lock and job$

SQL> select col#, obj#, comment$ from com$ where obj# = (select obj# from obj$
where name = 'DBA_SCHEDULER_RUNNING_JOBS' and owner# = 0) and col# is null;

no rows selected

SQL> select col#, obj#, comment$ from com$ where obj# = (select obj# from obj$
where name = 'DBA_SCHEDULER_RUNNING_JOBS' and owner# = 0);

COL# OBJ# COMMENT$
---------- ---------- ------------------------------------------------------------------------------------------
1 9386 Owner of the running scheduler job
2 9386 Name of the running scheduler job
3 9386 Subname of the running scheduler job (for a job running a chain step)
4 9386 Job style - regular, lightweight or volatile
7 9386 Process number of the slave process running the scheduler job
8 9386 Operating system process number of the slave process running the scheduler job
9 9386 Database instance number of the slave process running the scheduler job
10 9386 Resource consumer group of the session in which the scheduler job is running
11 9386 Time elapsed since the scheduler job started
12 9386 CPU time used by the running scheduler job, if available
13 9386 Owner of destination object (if used) else NULL
14 9386 Destination that this job is running on
15 9386 Owner of login credential used for this running job, if any
16 9386 Name of login credential used for this running job, if any
17 9386 Log id that will be used for this job run

15 rows selected.


I also checked $ORACLE_HOME/rdbms/admin and found the file
where "comment on table dba_scheduler_running_jobs is"
was missed - catschv.sql, looks like a bug for me...

Have a good day!

Tuesday, August 23, 2011

RDBMS Online Patching

(originally published at Pythian)
If you support Oracle RDBMS 11.2.0.2 and want to zero downtime applying
patches to databases then it is time to have a look at new possibility
of Online Patching delivered with 11.2.0.2 version and described here
RDBMS Online Patching Aka Hot Patching [ID 761111.1]

The syntax is "opatch apply online -connectString ... " but I could not find description
of the online option (only -connectString) using opatch -help (version 11.2.0.1.6)
although based on the note it is available since 11.1.0.6 version of opatch

And while going through README for the patch I found the blog
about online patching - Applying online patch on 11gr2
which helped me greatly to get proper syntax for online patching.

I had test RAC database with applied PSU3 and checked if there
were any online patches available on MOS. I found quite a few and downloaded
several of them to check what information "opatch query" will show.

Surprisingly, "opatch query" did not show any of them to be applicable online
but all of them had online directory with .pch file underneath and all
of them had "Online Installable Patch" in their README
for example 10040035: AROLTP-D: INSTANCE DIED WITH ORA-00600 [KCLPDC_21]
[oracle@r1 10040035]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch query .

Invoking OPatch 11.2.0.1.6
...
Need to shutdown Oracle instances: true
Patch is roll-backable: true
Patch is a "Patchset Update": false
Patch is a rolling patch: true
Patch has sql related actions: false
Patch is an online patch: false
Patch is a portal patch: false
Patch is an "auto-enabled" patch: false

List of platforms supported:
226: Linux x86-64

List of prereq patches:
12419331

List of overlay patches:
12419331

List of bugs to be fixed:
10040035: AROLTP-D: INSTANCE DIED WITH ORA-00600 [KCLPDC_21]
...
OPatch succeeded.

[oracle@r1 10040035]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch query -is_online_patch .

Invoking OPatch 11.2.0.1.6

Oracle Interim Patch Installer version 11.2.0.1.6
Copyright (c) 2011, Oracle Corporation. All rights reserved.


Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.6
OUI version : 11.2.0.2.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-08-29_01-41-03AM.log

--------------------------------------------------------------------------------
Patch is an online patch: false


OPatch succeeded.

[oracle@r1 10040035]$ tree .

.
|-- README.txt
|-- etc
| |-- config
| | |-- actions.xml
| | |-- deploy.xml
| | `-- inventory.xml
| `-- xml
| |-- GenericActions.xml
| `-- ShiphomeDirectoryStructure.xml
|-- files
| `-- lib
| `-- libserver11.a
| `-- kcl.o
`-- online
|-- etc
| |-- config
| | |-- actions.xml
| | |-- deploy.xml
| | `-- inventory.xml
| `-- xml
| |-- GenericActions.xml
| `-- ShiphomeDirectoryStructure.xml
`-- files
`-- hpatch
`-- bug10040035.pch

I have been able to apply all of them without any downtime and all
of them appeared to be ENABLED on both instances:
r1221> oradebug patch list


Patch File Name State
================ =========
bug9795214.pch ENABLED
bug10040035.pch ENABLED
bug10222480.pch ENABLED
bug11670161.pch ENABLED
bug11853815.pch ENABLED
bug9829397.pch ENABLED


Interesting enough that either for RAC or for standalone database
"opatch apply online" was converted to RAC mode "-all_nodes"
although patches were applied properly for both clustered and non-clustered
environments.
RAC database:
[oracle@r1 10040035]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch apply \

online -connectString r1221:sys:pwd:r1,r1222:sys:pwd:r2
Invoking OPatch 11.2.0.1.6

Oracle Interim Patch Installer version 11.2.0.1.6
Copyright (c) 2011, Oracle Corporation. All rights reserved.


Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.6
OUI version : 11.2.0.2.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-08-29_02-07-19AM.log


The patch should be applied/rolled back in '-all_nodes' mode only.
Converting the RAC mode to '-all_nodes' mode.
Applying interim patch '10040035' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
Verifying environment and performing prerequisite checks...

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y
Backing up files...

Patching component oracle.rdbms, 11.2.0.2.0...
The patch will be installed on active database instances.
Installing and enabling the online patch 'bug10040035.pch', on database 'r1221'.


Patching in all-node mode.

Updating nodes 'r2'
Apply-related files are:
FP = "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/copy_files.txt"
DP = "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/copy_dirs.txt"
MP = "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/make_cmds.txt"
RC = "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/remote_cmds.txt"

Instantiating the file "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/
copy_files.txt.instantiated" by replacing $ORACLE_HOME
in "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/copy_files.txt"
with actual path.
Propagating files to remote nodes...
Instantiating the file "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/
copy_dirs.txt.instantiated" by replacing $ORACLE_HOME
in "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/copy_dirs.txt"
with actual path.
Propagating directories to remote nodes...
Installing and enabling the online patch 'bug10040035.pch', on database 'r1222' on node 'r2'.

Patch 10040035 successfully applied
Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-08-29_02-07-19AM.log

OPatch succeeded.


non-RAC database
[oracle@oel5 11800959]$ /u02/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch apply online -connectString d122:sys:pwd:

Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation. All rights reserved.


Oracle Home : /u02/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.1
OUI version : 11.2.0.2.0
OUI location : /u02/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-08-29_15-27-30PM.log

Patch history file: /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt


The patch should be applied/rolled back in '-all_nodes' mode only.
Converting the RAC mode to '-all_nodes' mode.
ApplySession applying interim patch '11800959' to OH '/u02/app/oracle/product/11.2.0/dbhome_1'

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '11800959' for restore. This might take a while...
Backing up files affected by the patch '11800959' for rollback. This might take a while...

Patching component oracle.rdbms, 11.2.0.2.0...
The patch will be installed on active database instances.
Installing and enabling the online patch 'bug11800959.pch', on database 'd122'.

ApplySession adding interim patch '11800959' to inventory

Verifying the update...
Inventory check OK: Patch ID 11800959 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 11800959 are present in Oracle Home.

OPatch succeeded.

I think that conversion a little bit misleading for RAC database patching
because credentials should be entered for all instances of clustered database
and if you do not type it for one of them, there would not be any errors,
execution will be finished but patch will be applied only for nodes which credentials
are provided.

Online patches can be easily managed by oradebug and can be turned
on and off at each instance of RAC database or for the whole non-RAC database.
However "opatch lsinventory" does not show status of online patch
only if it is applied.
d122> oradebug patch list


Patch File Name State
================ =========
bug11800959.pch ENABLED

d122> oradebug patch disable bug11800959.pch
Statement processed.
d122> oradebug patch list

Patch File Name State
================ =========
bug11800959.pch DISABLED

d122> oradebug patch enable bug11800959.pch
Statement processed.
d122> oradebug patch list

Patch File Name State
================ =========
bug11800959.pch ENABLED

d122> oradebug patch disable bug11800959.pch term
Statement processed.
d122> oradebug patch list

Patch File Name State
================ =========
bug11800959.pch DISABLED

[oracle@oel5 u01]$ /u02/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch lsinventory
Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation. All rights reserved.


Oracle Home : /u02/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.1
OUI version : 11.2.0.2.0
OUI location : /u02/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-08-30_09-36-16AM.log

Patch history file: /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2011-08-30_09-36-16AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g 11.2.0.2.0
There are 1 products installed in this Oracle Home.


Interim patches (1) :

Patch (online) 11800959: applied on Mon Aug 29 15:27:55 EST 2011
Unique Patch ID: 13914499
Created on 8 Jul 2011, 03:51:56 hrs PST8PDT
Bugs fixed:
11800959



--------------------------------------------------------------------------------

d122> oradebug patch enable bug11800959.pch
Statement processed.
d122> oradebug patch list

Patch File Name State
================ =========
bug11800959.pch ENABLED


Oracle Support encourages to apply online patches only in cases when
downtime can not be scheduled immediately but strongly recommends to replace
them with normal patches that requires shutdown of instances.

And all my tests were done on test databases so I have not
taken any risk yet to do it on production. But the feature is very useful
and number of patches being steadily increased over last releases.

Have a good day!

Monday, August 22, 2011

CheckSystemSpace ignorance

(originally published at Pythian)
Every installation is unique. It can be the same software and hardware
but one installation will squease you like a lemon in jucier another one
will make you feel that you and only you is the boss here.

The way how it behaves depends not only on stars location and on
Saturn position in Zenith but also on how scripts finished, what SA did not
implement and change and how vigorously developers spent a day.

In any case you should put all your heart to the process either it is GUI
or silent installation, feel the breath of installer and give heed to every
entry appeared in the output log.

But I digressed...

Today the post is about one of the hidden parameters for OPatch which
is the main contributor to non-simple process of patching.

11.2.0.2.3 Grid Infrastructure patch failed at one of test RAC systems
and only log showed that CheckSystemSpace triggered the failure.
It was asking for ~3Gb of free space, but file system did not have that much.

Recalling -ingoreSysPreReqs option for runInstaller, I checked MOS
trying to find any option/parameter/variable that can allow to ignore
system space check to go further with installation.
Of course, if it was production system I would rather clean it or increase
space, but for sake of testing I decided to look for any option
that would "trick" opatch utility.

And finally it was found and helped me in that OPATCH_DEBUG variable
which after setting to true forced opatch to splash out lots of detailed
information together with list of different variables.

SKIP_VERIFY and SKIP_VERIFY_SPACE were among them and
I added SKIP_VERIFY_SPACE to opatch command line and output
was changed to another one but paying no heed to space issue.

before
/bin/su oracle -c ' /u01/app/grid/11.2.0/OPatch/opatch napply  \

/home/oracle/install/patches/11.2.0.2.3GI/12419353 -local -silent \
-ocmrf /home/oracle/install/patches/ocm.rsp \
-oh /u01/app/grid/11.2.0 '
...
Prerequisite check "CheckSystemSpace" failed.
The details are:
Required amount of space(3131607220) is not available.
UtilSession failed: Prerequisite check "CheckSystemSpace" failed.
...

after
[root@r1 ~]# /bin/su oracle -c ' /u01/app/grid/11.2.0/OPatch/opatch napply \

/home/oracle/install/patches/11.2.0.2.3GI/12419353 -local -silent \
-ocmrf /home/oracle/install/patches/ocm.rsp -oh \
/u01/app/grid/11.2.0 \
OPatch.SKIP_VERIFY_SPACE=true '
...
Prerequisite check "CheckApplicable" failed.
The details are:
....


And if it is used with CheckSystemSpace of prereq it does not avoid the check
anyway
/bin/su oracle -c ' /u01/app/grid/11.2.0/OPatch/opatch prereq  \

CheckSystemSpace \
-phBaseDir /home/oracle/install/patches/11.2.0.2.3GI/12419353 \
OPatch.SKIP_VERIFY_SPACE=true '
Invoking OPatch 11.2.0.1.6

Oracle Interim Patch Installer version 11.2.0.1.6

Copyright (c) 2011, Oracle Corporation. All rights reserved.

PREREQ session


Oracle Home : /u01/app/grid/11.2.0

Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.6
OUI version : 11.2.0.2.0
Log file location : /u01/app/grid/11.2.0/cfgtoollogs/opatch/opatch2011-08-25_23-46-59PM.log

Invoking prereq "checksystemspace"


Prereq "checkSystemSpace" failed.


The details are:

Required amount of space(3131607220) is not available.

OPatch succeeded.


Worth to note that those parameters can be added to "opatch napply"
or to "opatch apply" execution but not to "opatch auto" call
[root@r1 11.2.0.2.3GI]# /u01/app/grid/11.2.0/OPatch/opatch \

auto /home/oracle/install/patches/11.2.0.2.3GI \
-oh /u01/app/grid/11.2.0 OPatch.SKIP_VERIFY=true \
OPatch.SKIP_VERIFY_SPACE=true
Executing /usr/bin/perl /u01/app/grid/11.2.0/OPatch/crs/patch112.pl
-patchdir /home/oracle/install/patches -patchn 11.2.0.2.3GI
-oh /u01/app/grid/11.2.0 OPatch.SKIP_VERIFY=true
OPatch.SKIP_VERIFY_SPACE=true
-paramfile /u01/app/grid/11.2.0/crs/install/crsconfig_params
opatch auto log file location is
/u01/app/grid/11.2.0/OPatch/crs/../../cfgtoollogs/opatchauto2011-08-26_00-15-44.log
Invalid extra options passed: OPatch.SKIP_VERIFY=true
OPatch.SKIP_VERIFY_SPACE=true
Usage:
...


Have a good day!

Tuesday, July 26, 2011

MOS KB articles for July 27, 2011

Checking recent articles I noticed that there are lots of Master Note type of notes
appeared on MyOracleSupport that come like aggregation of different links
related to topics. Exadata, RAC, Data Guard, Data Warehousing, etc..

It might be very useful when problems appeared or when knowledge
about the technology should be gotten. I wish those topics had RSS
feeds that can keep you informed about changes in notes.

And regular review of the Hot Topics e-mails made my life easier today
when I got a ticket about failure of backup of RAC 11.2.0.2 database.
I immediately recalled the note
RAC BACKUP FAILS WITH ORA-00245: CONTROL FILE BACKUP OPERATION FAILED [ID 1268725.1]
where location of snapshot controlfile should be on a shared storage

So I hope the change of snapshot controlfile location to ASM will
help to resolve the issue, we'll see tomorrow...


Another note that attracted my attention is
RACcheck - RAC Configuration Audit Tool [ID 1268927.1]
The utility simplifies the audit process of RAC configuration and allows not to forget
all important bits and pieces of complex architecture.


Have a good day!


Wednesday, July 20, 2011

MOS KB articles for July 20, 2011

During search in knowledge base on My Oracle Support site noticed
that now list of prompts appears based on typed letters, words.

It can be helpful despite results of search do not even have the same
phrase which were selected from suggested list.

Another observation - combination "ORA-" does not show any match,
might be because of too long list of similarities?

Today's Hot Topics e-mail got list of articles related to patchsets,
their avalability and known issues. For instance, note about 11.2.0.2
patchset 11.2.0.2 Patch Set - Availability and Known Issues [ID 1179474.1]
(as well as other notes about patchsets) has a list of issues
introduced in the release and their Bug ID.
Although they are not fixed, information about problems can be helpful
especially in case of upgrades and patching.

And finally - July 2011 CPU patches came for oracle products yesterday.
Happy patching!

Have a good day!

Tuesday, July 19, 2011

MOS KB articles for July 19, 2011

Several articles in Hot Topics e-mails drew my attention today:

10.2.0.5.5 Grid Control Patch Set Update (PSU) [ID 1335690.1]
good to know that PSU5 as well as PSU4 are OMS pathes only.

"hcheck.sql" script to check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g [ID 136697.1]
script creates a package under SYS schema that checks data dictionary for common dictionary problems.

Master Note For Oracle Database Server Installation [ID 1156586.1]
it is helpful that the document has links to silent installation notes as well.

11g Grid Control: Overview of the EMCTL Options Available for Managing the OMS [ID 1188803.1] that article might be valuable for those who spends lots of time managing Grid Control.


Have a good day!