Vijay R. Dumpa Oracle Blog

I/O Resource Management(IORM) with Exadata

2012-01-16T11:47:00.003-05:00

I/O Resource Management(IORM) with Exadata

a)
-- How to configure IORM on cell nodes.

ODS and EDW- Level 1 (40%+40%=80%)
All other(EDWSTG and any other) - Level 2 (100% - that's nothing but overall 20%)
Note: IORM have 8 levels like DBRM.

-- To create IORM, need to create the IORMPLAN on each cell node
CellCLI> alter iormplan dbplan=((name='ODS', level=1, allocation=40),(name='EDW', level=1, allocation=40),(name=other,level=2,allocation=100))
IORMPLAN successfully altered

-- How to Activate
CellCLI> alter iormplan active

-- How to Inactivate
CellCLI> alter iormplan inactive

-- Check if the IORM is Active?
CellCLI> LIST IORMPLAN
atl02cel02_IORMPLAN active

-- Check if current IORM plan in place?
CellCLI> LIST IORMPLAN detail
name: atl02cel01_IORMPLAN
catPlan:
dbPlan: name=ODS,level=1,allocation=40
name=EDW,level=1,allocation=40
name=other,level=2,allocation=100
status: active

b)
-- Monitoring IORM with cellcli command.

-- How to check Small IO load per database
CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_SM
DB_IO_RQ_SM EDW 7,387,543 IO requests
DB_IO_RQ_SM ODS 5,262,853 IO requests
DB_IO_RQ_SM EDWSTG 11,526,325 IO requests

-- How to check Large IO load per database
CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_LG
DB_IO_RQ_LG EDW 13,210,476 IO requests
DB_IO_RQ_LG ODS 5,865,946 IO requests
DB_IO_RQ_LG EDWSTG 4,644,494 IO requests

-- How to check Small IO load/sec. per database, last minute
CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_SM_SEC
DB_IO_RQ_SM_SEC EDW 14.5 IO/sec
DB_IO_RQ_SM_SEC ODS 86.5 IO/sec
DB_IO_RQ_SM_SEC EDWSTG 135 IO/sec

-- How to check Large IO load/sec. per database, last minute
CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_LG_SEC
DB_IO_RQ_LG_SEC EDW 54.4 IO/sec
DB_IO_RQ_LG_SEC ODS 47.0 IO/sec
DB_IO_RQ_LG_SEC EDWSTG 73.5 IO/sec

-- How to check Small IO Waits per database. per min.
CellCLI> LIST METRICCURRENT where name=DB_IO_WT_SM
DB_IO_WT_SM EDW 35,576,746 ms
DB_IO_WT_SM ODS 5,686,954 ms
DB_IO_WT_SM EDWSTG 7,236,957 ms

-- How to check Large IO Waits per database. per min.
CellCLI> LIST METRICCURRENT where name=DB_IO_WT_LG
DB_IO_WT_LG EDW 2,284,982,657 ms
DB_IO_WT_LG ODS 384,891,454 ms
DB_IO_WT_LG EDWSTG 394,093,606 ms

How to find a SQL or session is running on which cell node and what it is doing?

2011-08-09T22:23:00.002-04:00

How to find a SQL or session is running on which cell node and what it is doing?

-- Active SQL from session ID: 1271:
UPDATE etl_job_control
SET start_dt = (SELECT end_dt
FROM etl_job_control
WHERE job_name = 's_m_network_stg'),
end_dt =
(SELECT DATE '1970-01-01' + ((SELECT MAX (mod_t)
FROM pin_user.event) / 24 / 60 / 60)
FROM DUAL),
last_update_dt = SYSDATE
WHERE job_name = :jn;

-- Wait event on the session
atl02db01:ODS1\sys: SQL> select event from gv$session where sid = 1271 and serial# = 31026;

EVENT
--------------------------------
cell multiblock physical read

-- Let's find the details from cell node
LIST ACTIVEREQUEST where sessionID=1271 and sessionSerNumber=31026 detail -- It looks like session runs on one cell node at any given time to get the data from the grid disk, as shown below session moved from cell 9 to 10.

atl02cel09:
CellCLI> LIST ACTIVEREQUEST where sessionID=1271 and sessionSerNumber=31026 detail
name: 5382
asmDiskGroupNumber: 3
asmFileIncarnation: 739372987
asmFileNumber: 2518
consumerGroupID: 4866
consumerGroupName: OTHER_GROUPS
dbID: 3108331340
dbName: ODS
dbRequestID: 5382
fileType: Datafile
id: 5382
instanceNumber: 1
ioBytes: 524288
ioBytesSofar: 0
ioGridDisk: DATA_CD_10_atl02cel09
ioOffset: 790293632
ioReason: "BufferCache Read"
ioType: CacheGet
objectNumber: 8104218
parentID: 5382
requestState: "Queued for Disk Read"
sessionID: 1271
sessionSerNumber: 31026
sqlID: 7p843mkscna11
tableSpaceNumber: 197

atl02cel10:
CellCLI> LIST ACTIVEREQUEST where sessionID=1271 and sessionSerNumber=31026 detail
name: 2301
asmDiskGroupNumber: 3
asmFileIncarnation: 731923539
asmFileNumber: 2059
consumerGroupID: 4866
consumerGroupName: OTHER_GROUPS
dbID: 3108331340
dbName: ODS
dbRequestID: 2301
fileType: Datafile
id: 2301
instanceNumber: 1
ioBytes: 376832
ioBytesSofar: 0
ioGridDisk: DATA_CD_01_atl02cel10
ioOffset: 669064480
ioReason: "BufferCache Read"
ioType: CacheGet
objectNumber: 8104222
parentID: 2301
requestState: "Queued for Disk Read"
sessionID: 1271
sessionSerNumber: 31026
sqlID: 7p843mkscna11
tableSpaceNumber: 197

-- Want to see all the active request from instance 1 for database ODS
CellCLI> LIST ACTIVEREQUEST where instanceNumber=1 and dbName=ODS

Exadata Smart Scan(cell offload processing), Exadata Smart Flash Cache and Exadata Storage Index Capability's

2011-07-31T21:23:00.005-04:00

Exadata Smart Scan(cell offload processing), Exadata Smart Flash Cache and Exadata Storage Index Capability's

a).
-- Exadata Smart Scan(cell offload processing)

Exadata Smart Scan processes queries at the storage layer, returning only relevant rows and columns to the database server.
As a result, much less data travels over fast 40GB Infiniband interconnect, dramatically improving the performance and concurrency of simple and complex queries.

-- Which predicates are supported by a cell? What happens when predicates contain functions or expressions?

a) Numeric functions: they can all be offloaded with a single exception, the
WIDTH_BUCKET function. For example the predicate "width_bucket(n,1,10,100) = 1"
is not offloaded.

b) Character functions returning character values: they can all be offloaded.

c) Character functions returning number values: they can all be offloaded.

d) Datetime functions:
DATE datatype is supported.
When a TIMESTAMP datatype is involved, offloading almost never happens.
While predicates like "d = sysdate" (note that "d" is a column of DATE datatype)
and "add_months(d,1) = to_date('01-01-2010','DD-MM-YYYY')" can be offloaded,
something like "add_months(d,1) = sysdate" cannot.
Every datetime function can be offloaded provided that it is not used along with
SYSDATE or CURRENT_DATE.

Note: There is no synchronization between the db layer and the storage layer for the purpose of offloading datetime functions. Whatever they would do, it is simply impossible to have exactly synchronized timers. Hence, inconsistent results might be generated. Therefore, IMO, the date/time for stuff like SYSDATE must be provided by the instance where a SQL statement is started.

months_between(d,sysdate) = 0
months_between(d,current_date) = 0
months_between(d,to_date('01-01-2010','DD-MM-YYYY')) = 0

e) NULL-related functions: they can all be offloaded.

-- List of the functions supporting offloading, is available through the V$SQLFN_METADATA view.
SELECT offloadable, count(DISTINCT name)
FROM v$sqlfn_metadata
GROUP BY offloadable;

-- Smart Scan Disablers
IOT
Clustered Tables
LOB
Rowdependencies Enabled

-- You can use the below query to see the "cell physical IO interconnect bytes returned by smart scan" in bytes
SELECT a.NAME, b.VALUE
FROM v$sysstat a, v$mystat b
WHERE a.statistic# = b.statistic#
AND ( a.NAME IN
('physical read total bytes', 'physical write total bytes',
'cell IO uncompressed bytes')
OR a.NAME LIKE 'cell physical%'
);

b).
-- How to create and drop Exadata Smart Flash Cache?

-- Drop flashcache (To flush Exadata Smart Flash Cache).
CellCLI> DROP FLASHCACHE

-- Create flashcache on all disks.
CellCLI> CREATE FLASHCACHE ALL

-- Flashcache cell disk and size deatils.
CellCLI> LIST FLASHCACHE detail
name: atlcel01_FLASHCACHE
cellDisk: FD_10_atlcel01,FD_00_atlcel01,FD_04_atlcel01,FD_14_atlcel01,FD_15_atlcel01,FD_02_atlcel01,FD_08_atlcel01,FD_07_atlcel01,FD_05_atlcel01,FD_09_atlcel01,FD_13_atlcel01,FD_03_atlcel01,FD_12_atlcel01,FD_01_atlcel01,FD_11_atlcel01,FD_06_atlcel01
creationTime: 2011-03-27T03:18:23-04:00
degradedCelldisks:
effectiveCacheSize: 365.25G
id: 40ef10f1-8c82-4419-b5a1-9d51706813fe
size: 365.25G
status: normal

-- To determine whether an object is currently being kept in the Exadata Smart Flash Cache

-- Get the object id
atldb01:ODS1\sys: SQL> select object_id, object_type from dba_objects where object_id = 8104207;

OBJECT_ID OBJECT_TYPE
---------- -------------------
8104207 TABLE PARTITION

-- Look at the segment statistics and get the DATAOBJ#
atldb01:ODS1\sys: SQL> select distinct inst_id, owner, object_name, ts#, obj#, dataobj# from gv$segment_statistics
2 where owner = 'PIN_USER'
3 and object_name = 'EVENT';

INST_ID OWNER OBJECT_NAME TS# OBJ# DATAOBJ#
---------- ------------------------------ ------------------------------ ---------- ---------- ----------
2 PIN_USER EVENT 197 8104207 8104207
1 PIN_USER EVENT 197 8104202 8104202
3 PIN_USER EVENT 197 8104201 8104201
2 PIN_USER EVENT 197 8104218 8104218
2 PIN_USER EVENT 197 8104221 8104221
2 PIN_USER EVENT 197 8104212 8104212
2 PIN_USER EVENT 197 8104215 8104215
2 PIN_USER EVENT 197 8104227 8104227
1 PIN_USER EVENT 197 8104218 8104218
1 PIN_USER EVENT 197 8104212 8104212
1 PIN_USER EVENT 197 8104205 8104205

-- List the object details from Flash Cache.
CellCLI> LIST FLASHCACHECONTENT where objectNumber=8104207 and tableSpaceNumber=197 and dbUniqueName=PODS detail
cachedKeepSize: 0
cachedSize: 4194304
dbID: 3108331340
dbUniqueName: PODS
hitCount: 5972
missCount: 8820
objectNumber: 8104207
tableSpaceNumber: 197

Note: DBA can cache an object by setting CELL_FLASH_CACHE setting to KEEP. The default value of this setting is DEFAULT.

When you are accessing the object data from the Flash Cache, you should see high number of optimized request("physical read requests optimized" and "cell flash cache read hits") than the high number of I/O requests("physical read total IO requests").

-- You can use the below query to see the "flash cache read hits" in bytes.
SELECT a.NAME, b.VALUE
FROM v$sysstat a, v$mystat b
WHERE a.statistic# = b.statistic#
AND ( a.NAME LIKE '%flash cache read hits'
OR a.NAME LIKE 'cell physical%'
OR a.NAME LIKE 'physical read tot%'
OR a.NAME LIKE 'physical read req%'
);

c).
-- Storage Index Capability's

Storage Indexes are memory structures which do not persist when the Exadata cells are restarted. They are dynamically built when tables are referenced for the first time after the cells restart.

-- You can use the below query to see the savings of Storage Index in MB.
SELECT a.NAME, b.VALUE / 1024 / 1024 value_in_mb
FROM v$sysstat a, v$mystat b
WHERE a.statistic# = b.statistic#
AND ( a.NAME IN
('physical read total bytes', 'physical write total bytes',
'cell IO uncompressed bytes')
OR a.NAME LIKE 'cell physical%'
);

Value of "cell physical IO bytes saved by storage index" shows the savings of the physical I/O by storage index and the total Physical I/O is recorded under "physical read total bytes".

Queries that benefit from storage index can execute more quickly using fewer resources which allows other workloads to use the unused I/O resources.

How to setup notifications on cell node:

2011-07-10T17:27:00.008-04:00

How to setup notifications on cell node:

-- Setting up notification on a cell
CellCLI> ALTER CELL smtpServer='smtpmail.corp.dwire.com', -
smtpFromAddr='OracleSupport@dwire.com', -
smtpFrom='DWIRE Oracle Support', -
smtpToAddr='OracleSupport@dwire.com,Vijay.Dumpa@dwire.com', -
notificationPolicy='critical,warning,clear', -
notificationMethod='mail,snmp'

-- List the notification details
CellCLI> list cell detail
name: atl02cel01
bmcType: IPMI
cellVersion: OSS_11.2.1.2.6_LINUX.X64_100511
cpuCount: 16
fanCount: 12/12
fanStatus: normal
id: 1009XFG023
interconnectCount: 3
interconnect1: bond0
iormBoost: 0.0
ipaddress1: 192.168.10.1/22
kernelVersion: 2.6.18-128.1.16.0.1.el5
makeModel: SUN MICROSYSTEMS SUN FIRE X4275 SERVER SAS
metricHistoryDays: 7
notificationMethod: mail,snmp
notificationPolicy: critical,warning,clear
offloadEfficiency: 3.6M
powerCount: 2/2
powerStatus: normal
smtpFrom: "DWIRE Oracle Support"
smtpFromAddr: OracleSupport@dwire.com
smtpServer: smtpmail.corp.dwire.com
smtpToAddr: OracleSupport@dwire.com,Vijay.Dumpa@dwire.com
snmpSubscriber: host=catl0sm41,port=162,community=celadmin
host=catl0sm58,port=162,community=celadmin
host=catl0sm59,port=162,community=celadmin
host=catl0sm157,port=162,community=celadmin
status: online
temperatureReading: 25.0
temperatureStatus: normal
upTime: 46 days, 19:18
cellsrvStatus: running
msStatus: running
rsStatus: running

CellCLI> exit
quitting

CellCLI> ALTER CELL VALIDATE mail -- A test message is sent using mail configuration.
CellCLI> ALTER CELL VALIDATE mail
Cell atl02cel01 successfully altered

-- Test email content, received in the outlook
Subject: CELL atl02cel01 Test Message
This test e-mail message from Oracle Cell atl02cel01 indicates successful configuration of your e-mail address and mail server.

CellCLI> ALTER CELL VALIDATE configuration -- To verify firmware configuration. (Takes a minute to return)
CellCLI> ALTER CELL VALIDATE configuration
Cell atl02cel01 successfully altered

Note: Need to execute on each cell node.

-- How to disable the notification alert?
cellcli -e alter cell notificationMethod=null

-- How to enable the notification alert back?
cellcli -e alter cell notificationMethod='mail,snmp'

Exadata Disk creation and Management:

2011-07-10T16:51:00.006-04:00

Exadata Disk creation and Management:

Exadata Storage Layout:
PHYSICAL DISK -> LUN -> CELL DISK -> GRID DISK -> ASM DISK

a).
-- List LUN and PHYSICAL DISK.
CellCLI> LIST LUN -- List of all LUN's on a cell.
CellCLI> LIST LUN
0_0 0_0 normal
0_1 0_1 normal
0_2 0_2 normal
0_3 0_3 normal
0_4 0_4 normal
0_5 0_5 normal
0_6 0_6 normal
0_7 0_7 normal
0_8 0_8 normal
0_9 0_9 normal
0_10 0_10 normal
0_11 0_11 normal
1_0 1_0 normal
1_1 1_1 normal
1_2 1_2 normal
1_3 1_3 normal
2_0 2_0 normal
2_1 2_1 normal
2_2 2_2 normal
2_3 2_3 normal
4_0 4_0 normal
4_1 4_1 normal
4_2 4_2 normal
4_3 4_3 normal
5_0 5_0 normal
5_1 5_1 normal
5_2 5_2 normal
5_3 5_3 normal

CellCLI> LIST LUN where disktype = harddisk -- List all the hard disks on a cell.
CellCLI> LIST LUN where disktype = harddisk
0_0 0_0 normal
0_1 0_1 normal
0_2 0_2 normal
0_3 0_3 normal
0_4 0_4 normal
0_5 0_5 normal
0_6 0_6 normal
0_7 0_7 normal
0_8 0_8 normal
0_9 0_9 normal
0_10 0_10 normal
0_11 0_11 normal

CellCLI> LIST LUN where disktype = flashdisk -- List all the flash disks on a cell.
CellCLI> LIST LUN where disktype = flashdisk
1_0 1_0 normal
1_1 1_1 normal
1_2 1_2 normal
1_3 1_3 normal
2_0 2_0 normal
2_1 2_1 normal
2_2 2_2 normal
2_3 2_3 normal
4_0 4_0 normal
4_1 4_1 normal
4_2 4_2 normal
4_3 4_3 normal
5_0 5_0 normal
5_1 5_1 normal
5_2 5_2 normal
5_3 5_3 normal

CellCLI> LIST LUN where celldisk = null -- List all the LUN's not associated with a cell disk.
CellCLI> LIST LUN where celldisk = null

CellCLI> LIST LUN where name = 0_7 detail -- Check isSystemLun=FALSE, this indicates that LUN is not located on a system disk.
CellCLI> LIST LUN where name = 0_7 detail
name: 0_7
cellDisk: CD_07_atl02cel01
deviceName: /dev/sdh
diskType: HardDisk
id: 0_7
isSystemLun: FALSE
lunAutoCreate: FALSE
lunSize: 1861.712890625G
lunUID: 0_7
physicalDrives: 24:7
raidLevel: 0
lunWriteCacheMode: "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"
status: normal

CellCLI> LIST PHYSICALDISK where name = 24:7 detail -- List the physical disk attributes (pass the "physicalDrives" name)
CellCLI> LIST PHYSICALDISK where name = 24:7 detail
name: 24:7
deviceId: 23
diskType: HardDisk
enclosureDeviceId: 24
errMediaCount: 0
errOtherCount: 0
foreignState: false
luns: 0_7
makeModel: "HITACHI H7220AA30SUN2.0T"
physicalFirmware: JKAOA28A
physicalInsertTime: 2010-07-13T21:22:00-04:00
physicalInterface: sata
physicalSerial: JK1130YAHBXL2T
physicalSize: 1862.6559999994934G
slotNumber: 7
status: normal

b).
-- How to create Cell disk?

CellCLI> LIST CELLDISK -- List all the cell disks on a cell node.
CellCLI> LIST CELLDISK
CD_00_atl02cel01 normal
CD_01_atl02cel01 normal
CD_02_atl02cel01 normal
CD_03_atl02cel01 normal
CD_04_atl02cel01 normal
CD_05_atl02cel01 normal
CD_06_atl02cel01 normal
CD_07_atl02cel01 normal
CD_08_atl02cel01 normal
CD_09_atl02cel01 normal
CD_10_atl02cel01 normal
CD_11_atl02cel01 normal
FD_00_atl02cel01 normal
FD_01_atl02cel01 normal
FD_02_atl02cel01 normal
FD_03_atl02cel01 normal
FD_04_atl02cel01 normal
FD_05_atl02cel01 normal
FD_06_atl02cel01 normal
FD_07_atl02cel01 normal
FD_08_atl02cel01 normal
FD_09_atl02cel01 normal
FD_10_atl02cel01 normal
FD_11_atl02cel01 normal
FD_12_atl02cel01 normal
FD_13_atl02cel01 normal
FD_14_atl02cel01 normal
FD_15_atl02cel01 normal

-- Hard Disk
CellCLI> LIST CELLDISK where name = CD_07_atl02cel01 detail
name: CD_07_atl02cel01
comment:
creationTime: 2010-07-14T19:56:09-04:00
deviceName: /dev/sdh
devicePartition: /dev/sdh
diskType: HardDisk
errorCount: 0
freeSpace: 560M
freeSpaceMap: offset=1832.046875G,size=560M
id: 00000129-d363-0901-0000-000000000000
interleaving: none
lun: 0_7
raidLevel: 0
size: 1861.703125G
status: normal

CellCLI> CREATE CELLDISK CD_07_atl02cel01 LUN='0_7' HARDDISK

-- Flash Disk
CellCLI> LIST CELLDISK where name = FD_00_atl02cel01 detail
name: FD_00_atl02cel01
comment:
creationTime: 2011-03-27T03:17:59-04:00
deviceName: /dev/sdq
devicePartition: /dev/sdq
diskType: FlashDisk
errorCount: 0
freeSpace: 0
id: caf0be8d-4061-451a-a9da-090945a9c8d5
interleaving: none
lun: 1_0
size: 22.875G
status: normal

CellCLI> CREATE CELLDISK FD_00_atl02cel01 LUN='1_0' FLASHDISK

c).
-- How to create Grid disk?
All the grid disks are created on cell disks (HARDDISK, not FLASHDISK).

CellCLI> LIST GRIDDISK -- List all the grid disks on a cell node.
CellCLI> LIST GRIDDISK
DATA_CD_00_atl02cel01 active
DATA_CD_01_atl02cel01 active
DATA_CD_02_atl02cel01 active
DATA_CD_03_atl02cel01 active
DATA_CD_04_atl02cel01 active
DATA_CD_05_atl02cel01 active
DATA_CD_06_atl02cel01 active
DATA_CD_07_atl02cel01 active
DATA_CD_08_atl02cel01 active
DATA_CD_09_atl02cel01 active
DATA_CD_10_atl02cel01 active
DATA_CD_11_atl02cel01 active
RECO_CD_00_atl02cel01 active
RECO_CD_01_atl02cel01 active
RECO_CD_02_atl02cel01 active
RECO_CD_03_atl02cel01 active
RECO_CD_04_atl02cel01 active
RECO_CD_05_atl02cel01 active
RECO_CD_06_atl02cel01 active
RECO_CD_07_atl02cel01 active
RECO_CD_08_atl02cel01 active
RECO_CD_09_atl02cel01 active
RECO_CD_10_atl02cel01 active
RECO_CD_11_atl02cel01 active
SYSTEMDG_CD_02_atl02cel01 active
SYSTEMDG_CD_03_atl02cel01 active
SYSTEMDG_CD_04_atl02cel01 active
SYSTEMDG_CD_05_atl02cel01 active
SYSTEMDG_CD_06_atl02cel01 active
SYSTEMDG_CD_07_atl02cel01 active
SYSTEMDG_CD_08_atl02cel01 active
SYSTEMDG_CD_09_atl02cel01 active
SYSTEMDG_CD_10_atl02cel01 active
SYSTEMDG_CD_11_atl02cel01 active

CellCLI> LIST GRIDDISK where celldisk=CD_07_atl02cel01 detail -- List the different grid disks names on one celldisk and the OFFSET is starting point on the physical disk.
CellCLI> LIST GRIDDISK where celldisk=CD_07_atl02cel01 detail
name: DATA_CD_07_atl02cel01
availableTo:
cellDisk: CD_07_atl02cel01
comment:
creationTime: 2010-07-14T19:58:53-04:00
diskType: HardDisk
errorCount: 0
id: 00000129-d365-88f0-0000-000000000000
offset: 32M
size: 1582G
status: active

name: RECO_CD_07_atl02cel01
availableTo:
cellDisk: CD_07_atl02cel01
comment:
creationTime: 2010-08-20T12:27:25-04:00
diskType: HardDisk
errorCount: 0
id: 0000012a-9053-7f16-0000-000000000000
offset: 1582G
size: 250G
status: active

name: SYSTEMDG_CD_07_atl02cel01
availableTo:
cellDisk: CD_07_atl02cel01
comment:
creationTime: 2010-07-14T19:56:50-04:00
diskType: HardDisk
errorCount: 0
id: 00000129-d363-a6e5-0000-000000000000
offset: 1832.59375G
size: 29.109375G
status: active

CellCLI> CREATE GRIDDISK DATA_CD_07_atl02cel01 celldisk=CD_07_atl02cel01,size=1582G,offset=32M -- Where "offset" is starting point on the disk.
CellCLI> CREATE GRIDDISK RECO_CD_07_atl02cel01 celldisk=CD_07_atl02cel01,size=250G,offset=1582G
CellCLI> CREATE GRIDDISK SYSTEMDG_CD_07_atl02cel01 celldisk=CD_07_atl02cel01,size=29.109375G,offset=1832.59375G

d).
-- How to create ASM disk on Exadata?

CellCLI> LIST GRIDDISK attributes name, size, asmmodestatus where asmmodestatus='UNUSED' -- To identify all the unused grid disks to create ASM disks.

asmca
(or)
. oraenv
+ASM1

sqlplus / as sysasm

set linesize 200
col path for a50

-- No CANDIDATE disks.
SQL> select name, header_status, path from v$asm_disk
2 where header_status <> 'MEMBER';

no rows selected

-- Print Existing disk name and the paths.
SQL> select * from (select name, header_status, path from v$asm_disk order by name)
2 where rownum < 16;

NAME HEADER_STATU PATH
------------------------- ------------ --------------------------------------------------
DATA_CD_00_ATL02CEL01 MEMBER o/192.168.10.1/DATA_CD_00_atl02cel01
DATA_CD_00_ATL02CEL02 MEMBER o/192.168.10.2/DATA_CD_00_atl02cel02
DATA_CD_00_ATL02CEL03 MEMBER o/192.168.10.3/DATA_CD_00_atl02cel03
DATA_CD_00_ATL02CEL04 MEMBER o/192.168.10.4/DATA_CD_00_atl02cel04
DATA_CD_00_ATL02CEL05 MEMBER o/192.168.10.5/DATA_CD_00_atl02cel05
DATA_CD_00_ATL02CEL06 MEMBER o/192.168.10.6/DATA_CD_00_atl02cel06
DATA_CD_00_ATL02CEL07 MEMBER o/192.168.10.7/DATA_CD_00_atl02cel07
DATA_CD_00_ATL02CEL08 MEMBER o/192.168.10.8/DATA_CD_00_atl02cel08
DATA_CD_00_ATL02CEL09 MEMBER o/192.168.10.9/DATA_CD_00_atl02cel09
DATA_CD_00_ATL02CEL10 MEMBER o/192.168.10.10/DATA_CD_00_atl02cel10
DATA_CD_01_ATL02CEL01 MEMBER o/192.168.10.1/DATA_CD_01_atl02cel01
DATA_CD_01_ATL02CEL02 MEMBER o/192.168.10.2/DATA_CD_01_atl02cel02
DATA_CD_01_ATL02CEL03 MEMBER o/192.168.10.3/DATA_CD_01_atl02cel03
DATA_CD_01_ATL02CEL04 MEMBER o/192.168.10.4/DATA_CD_01_atl02cel04
DATA_CD_01_ATL02CEL05 MEMBER o/192.168.10.5/DATA_CD_01_atl02cel05

15 rows selected.

col failgroup_type for a15;
SQL> select * from (select name, header_status, path, failgroup, failgroup_type, sector_size from v$asm_disk order by name)
2 where rownum < 16;

NAME HEADER_STATU PATH FAILGROUP FAILGROUP_TYPE SECTOR_SIZE
------------------------- ------------ --------------------------------------------- ----------- ------------------ -------------
DATA_CD_00_ATL02CEL01 MEMBER o/192.168.10.1/DATA_CD_00_atl02cel01 ATL02CEL01 REGULAR 512
DATA_CD_00_ATL02CEL02 MEMBER o/192.168.10.2/DATA_CD_00_atl02cel02 ATL02CEL02 REGULAR 512
DATA_CD_00_ATL02CEL03 MEMBER o/192.168.10.3/DATA_CD_00_atl02cel03 ATL02CEL03 REGULAR 512
DATA_CD_00_ATL02CEL04 MEMBER o/192.168.10.4/DATA_CD_00_atl02cel04 ATL02CEL04 REGULAR 512
DATA_CD_00_ATL02CEL05 MEMBER o/192.168.10.5/DATA_CD_00_atl02cel05 ATL02CEL05 REGULAR 512
DATA_CD_00_ATL02CEL06 MEMBER o/192.168.10.6/DATA_CD_00_atl02cel06 ATL02CEL06 REGULAR 512
DATA_CD_00_ATL02CEL07 MEMBER o/192.168.10.7/DATA_CD_00_atl02cel07 ATL02CEL07 REGULAR 512
DATA_CD_00_ATL02CEL08 MEMBER o/192.168.10.8/DATA_CD_00_atl02cel08 ATL02CEL08 REGULAR 512
DATA_CD_00_ATL02CEL09 MEMBER o/192.168.10.9/DATA_CD_00_atl02cel09 ATL02CEL09 REGULAR 512
DATA_CD_00_ATL02CEL10 MEMBER o/192.168.10.10/DATA_CD_00_atl02cel10 ATL02CEL10 REGULAR 512
DATA_CD_01_ATL02CEL01 MEMBER o/192.168.10.1/DATA_CD_01_atl02cel01 ATL02CEL01 REGULAR 512
DATA_CD_01_ATL02CEL02 MEMBER o/192.168.10.2/DATA_CD_01_atl02cel02 ATL02CEL02 REGULAR 512
DATA_CD_01_ATL02CEL03 MEMBER o/192.168.10.3/DATA_CD_01_atl02cel03 ATL02CEL03 REGULAR 512
DATA_CD_01_ATL02CEL04 MEMBER o/192.168.10.4/DATA_CD_01_atl02cel04 ATL02CEL04 REGULAR 512
DATA_CD_01_ATL02CEL05 MEMBER o/192.168.10.5/DATA_CD_01_atl02cel05 ATL02CEL05 REGULAR 512

15 rows selected.

col name for a10
col compatibility for a10
col database_compatibility for a22
SQL> select name, allocation_unit_size, block_size, compatibility, database_compatibility from v$asm_diskgroup;

NAME ALLOCATION_UNIT_SIZE BLOCK_SIZE COMPATIBIL DATABASE_COMPATIBILITY
---------- -------------------- ---------- ---------- ----------------------
SYSTEMDG 4194304 4096 11.2.0.0.0 11.2.0.0.0
DATA 4194304 4096 11.2.0.0.0 11.2.0.0.0
RECO 4194304 4096 11.2.0.0.0 11.2.0.0.0

. oraenv
+ASM1

sqlplus / as sysasm

-- Createing SYSTEMDG diskgroup:
CREATE DISKGROUP SYSTEMDG NORMAL REDUNDANCY
DISK 'o/*/SYSTEMDG*'
ATTRIBUTE 'AU_SIZE' = '4M',
'cell.smart_scan_capable'='TRUE',
'compatible.rdbms'='11.2.0.0',
'compatible.asm'='11.2.0.0';

-- Createing DATA diskgroup
CREATE DISKGROUP DATA NORMAL REDUNDANCY
DISK 'o/*/DATA*'
ATTRIBUTE 'AU_SIZE' = '4M',
'cell.smart_scan_capable'='TRUE',
'compatible.rdbms'='11.2.0.0',
'compatible.asm'='11.2.0.0';

-- Createing RECO diskgroup
CREATE DISKGROUP RECO NORMAL REDUNDANCY
DISK 'o/*/RECO*'
ATTRIBUTE 'AU_SIZE' = '4M',
'cell.smart_scan_capable'='TRUE',
'compatible.rdbms'='11.2.0.0',
'compatible.asm'='11.2.0.0';

Exadata V2 Storage Server (Cell node) Architecture and Management:

2011-01-02T10:58:00.013-05:00

Exadata V2 Storage Server (Cell node) Architecture and Management:

Architecture Overview:

Storage Servers:
The Exadata Storage Server is a SAN storage device specifically built for Oracle database use. Each holds 12 SAS or SATA disks (2 TB(SAS)/7 TB(SATA) total raw capacity),
dual Xeon CPUs, dual InfiniBand, and 384 GB of flash memory. Smart Scan support reduces the data that must travel over the InfiniBand network,
while Hybrid Columnar Compression reduces data footprint by 4 to 50 times, depending on the data and compression mode.

- Each Exadata storage cell has 12 disks.
- One physical disk is called LUN and is also called cell disk.
- A grid disk is part of a cell disk.
- A disk group is made of many grid disks.

- On the first two disks, the first 4 partitions (29GB) are reserved for system software. The two disks contain mirror copy.
- On the other 10 disks, 29GB can not be used. These 10 disks can be utilized by creating DBFS file system.
- So, Exdata creates a file system of 290GB called SYSTEMDG for FULL RAC cluster.
- SYSTEMDG contains OCR and Votedisk information.

ExadataCell Disk Storage Capacity:
. 12 x 600 GB SAS 15K rpm disks (7.2 TB/cell @ 100 TB total) - But actual size is about 558GB. A little bit of space is also kept aside for celldisk metadata ~48MB per disk.
. 12 x 2TB SATA disks (24 TB/cell @ 336 TB total)
. 4 x 96 GB Sun Flash Cards (384GB/cell @ total 5TB) - But when execute celci command 'list flashcache' returns 365.25.
- A little bit of space is also kept aside for celldisk metadata on these cards too.

Exadata Disk groups:
DATA (Data)
RECO (Redo logs, Archive logs and Flash Recovery Area)
SYSTEMDG (OCR and Votedisk)

Cell node unique feature:
Smart Scans
Hybrid Columnar Compression
Storage Indexes
Flash Cache
ExadataI/O Resource Management in Multi-Database Environment

Background Processes in the Exadata Cell Environment on database server:
The background processes for the database and Oracle ASM instance for an Exadata
Cell environment are the same as other environments, except for the following background process:

- diskmon Process - The diskmon process is a fundamental component of Exadata Cell, and is responsible for implementing I/O fencing.

- XDMG Process (Exadata Automation Manager)
Its primary task is to watch for inaccessible disks and cells, and to detect when the disks and cells become accessible.

- XDWK Process (Exadata Automation Worker)
The XDWK process begins when asynchronous actions, such as ONLINE, DROP or ADD for an Oracle ASM disk are requested by the XDMG process.
The XDWK process will stop after 5 minutes of inactivity.

Output:
> ps -ef | egrep "diskmon|xdmg|xdwk"
oracle 4684 4206 0 06:42 pts/1 00:00:00 egrep diskmon|xdmg|xdwk
oracle 10321 1 0 2010 ? 00:38:15 /u01/app/11.2.0/grid/bin/diskmon.bin -d -f
oracle 10858 1 0 2010 ? 00:00:18 asm_xdmg_+ASM1

As a departure from ASM storage technology that uses a process architecture borrowed from database instances,
the storage servers have a brand new set of processes to manage disk I/O. They are:
- RS, the restart service. Performing a similar role to SMON, RS monitors other processes, and automatically restarts them if they fail unexpectedly.
RS also handles planned restarts in conjunction with software updates.
The main cellrssrm process spawns several helper processes, including cellrsbmt, cellrsbkm, cellrsomt, and cellrsssmt.

- MS, the management service. MS is the back-end process that processes configuration and monitoring commands. It communicates with cellcli, described in the next section.
MS is written in Java, unlike the other background processes which are distributed in binary form and are likely written in C.

- CELLSRV, the cell service. CELLSRV handles the actual I/O processing of the storage server.
It is not uncommon to see heavy usage from CELLSRV process threads during periods of heavy load.
Among other things, CELLSRV provides:
. Communication with database nodes using the iDB/RDS protocols over the InfiniBand network
. Disk I/O with the underlying cell disks
. Offload of SQL processing from database nodes
. I/O resource management, prioritizing I/O requests based on a defined policy

- I/O Resource Manager (IORM). Enables storage grid by prioritizing I/Os to ensure predictable performance

Cell node Management Overview:
DBA's login as OS user "celladmin" to manage cell nodes.
Each cell node internally run ASM instance to manage cell node disks. This means, you can't see the ASM pmon process on the cell node.

Cell Admin. Tool's: cellcli and dcli.
Cell monitoring Tool's: OSWatcher, ORION (I/O performance benchmarking tool) and ADRCI

Cell Nodes Logs and Traces:
$ADR_BASE/diag/asm/cell/`hostname`/trace/alert.log
$ADR_BASE/diag/asm/cell/`hostname`/trace/ms-odl.*
$ADR_BASE/diag/asm/cell/`hostname`/trace/svtrc__0.trc -- ps -ef | grep "cellsrv 100"
$ADR_BASE/diag/asm/cell/`hostname`/incident/*

/var/log/messages*, dmesg
/var/log/sa/*
/var/log/cellos/*

cellcli -e list alerthistory

$OSSCONF/cellinit.ora -- #CELL Initialization Parameters
$OSSCONF/cell_disk_config.xml
$OSSCONF/griddisk.owners.dat
$OSSCONF/cell_bootstrap.ora

/opt/oracle/cell/cellsrv/deploy/log/cellcli.lst*

$OSSCONF/alerts.xml
$OSSCONF/metrics/*
oswatcher data

df -h -> check if /opt/oracle file system full? /opt/oracle only 2GB in size on cell node !!!

Where :
$OSSCONF is: /opt/oracle/cell11.2.1.3.1_LINUX.X64_100818.1/cellsrv/deploy/config
$ADR_BASE is: /opt/oracle/cell11.2.1.3.1_LINUX.X64_100818.1/log

Cell Check and shutdown/startup commands:
Note: For full list of commands use: cellcli -e help

cellcli -e alter cell shutdown services all
cellcli -e alter cell startup services all
cellcli -e alter cell shutdown services cellsrv
cellcli -e alter cell restart services cellsrv
cellcli -e list lun detail
cellcli -e list griddisk detail
cellcli -e list celldisk detail
cellcli -e list physicaldisk detail
cellcli -e list flashcache detail
cellcli -e list physicaldisk attributes name, diskType, luns, status
cellcli -e list physicaldisk where disktype=harddisk attributes physicalfirmware
cellcli -e list lun attributes name, diskType, isSystemLun, status

imagehistory (root/sudo)
imageinfo (root/sudo)
service celld status (root/sudo)
lsscsi | grep MARVELL

Smart scan layers:
Smart scan involves multiple layers of code
KDS/KTR/KCBL - data layers in rdbms
KCFIS - smart scan layer in rdbms
Predicate Disk - smart scan layer in cellsrv
Storage index - IO avoidance optimization in cellsrv
Flash IO - IO layer in cellsrv to fetch data from flash cache
Block IO - IO layer in cellsrv to fetch data from hard-disks
FPLIB - filtering library in cellsrv

How to Isolate the Issue Whether or Not it's Exadata Related?
Issue can be?
Wrong results when running the query on an Exadata DB -- I personally faced this issue.
Query is slower when running on an exadata database

Is smart scan issue?
Cell_offload_processing=false (default true)
If the problem does not occurs, it’s the smart scan issue.

Is this a FPLIB issue?
_kcfis_cell_passthru_enabled=true (default false)
If the problem does not occurs, it’s the FPLIB issue

Is storage index issue?
_kcfis_storageidx_disabled=true (default false)
Problem still occurs, it’s not a storage index issue.

Is flash cache issue?
For 11.2.0.2, _kcfis_keep_in_cellfc_enabled=false (default true) do not use flash cache
For 11.2.0.1, _kcfis_control1=1 (default 0)
Problem still occurs, it’s not a flash cache problem.

Cell related Database view's:
select * from sys.GV_$CELL_STATE;
select * from sys.GV_$CELL;
select * from sys.GV_$CELL_THREAD_HISTORY;
select * from sys.GV_$CELL_REQUEST_TOTALS;
select * from sys.GV_$CELL_CONFIG;

Bloom filter in Exadata:
In Oracle 10g concept of bloom filtering was introduced.
When two tables are joined via a hash join, the first table (typically the smaller table) is scanned and the rows that satisfy the ‘where’ clause predicates (for that table) are used to create a hash table.
During the hash table creation a bit vector or bloom filter is also created based on the join column.
The bit vector is then sent as an additional predicate to the second table scan.
After the ‘where’ clause predicates have been applied to the second table scan, the resulting rows will have their join column hashed and it will be compared to values in the bit vector.
If a match is found in the bit vector that row will be sent to the hash join. If no match is found then the row will be disregarded.
On Exadata the bloom filter or bit vector is passed as an additional predicate so it will be overloaded to the storage cells making bloom filtering very efficient.

How to Identify a Bloom Filter in an Execution plan:
You can identify a bloom filter in a plan when you see :BF0000 in the Name column of the execution plan.

To disable the feature, the initialization parameter _bloom_pruning_enabled must be set to FALSE.

Just want to say few words about other Exadata components too:

The Sun Oracle Exadata Database Machine hardware consists of preconfigured Oracle Database servers connected to Sun Oracle Exadata Storage Servers
by an InfiniBand fabric. Each of these has been configured to take advantage of the latest advances in Oracle database technology.

Database Servers:
Industry-standard Oracle Database 11gR2 servers feature advanced software that makes the extreme performance of Exadata possible.
Automatic Storage Management (ASM) provides advanced storage management capabilities; the Database Resource Manager (DBRM) lets users
prioritize the resources available to each database; and the Intelligent Database protocol (IDB) allows Smart Scan offloading of database queries
to the Storage Servers, greatly reducing network overhead.

InfiniBand switch:
At 40 Gbit/s on each port, the InfiniBand network linking the Exadata database servers to the Storage Servers is ten times as fast as Fibre Channel,
with lower latency. Multipathing protects against network failures.

Management switch:
A single Cisco Catalyst 4948 48-port gigabit Ethernet switch handles management traffic.

KVM and rack:
One 32-port Avocent KVM switch with associated keyboard/mouse drawer provides console access to database servers and storage cells.
The switch is IP-enabled, meaning remote console access is available either via the individual system ILOM ports or the KVM switch.
All the components are housed in a 42U Sun 1242E rack with integrated zero-U power distribution units.

What makes Exadata different from regular RAC?:

2010-12-22T16:34:00.009-05:00

What makes Exadata different from regular RAC?
We recently migrated 6 of our 10+ TB production databases to Exadata server.

1. Smart scan:

To disable smart scan set cell_offload_processing=FALSE (default TRUE) for that database.

List of cell DB parameters:
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offload_compaction string ADAPTIVE
cell_offload_decryption boolean TRUE
cell_offload_parameters string
cell_offload_plan_display string AUTO
cell_offload_processing boolean TRUE
cell_partition_large_extents string DEFAULT

2. Flash cache:

- Frequently accessed data and index are automatically cached.
- control file read and write are cached.
- File header read and writes are cached.
- DBA's can pin objects in flash cache.

- table scan data is not cached.
- backup data is not cached.
- DB pump data is not cached.
- data file formating data is not cached.
- IO to mirror copy is not cached.

- DBA can cache an object by setting CELL_FLASH_CACHE setting to KEEP. The default value of this setting is DEFAULT.

3. Exadata Hybrid Columnar Compression:

EHCC is not pure columnar, it is hybrid columnar

Multiple Levels of compression
- Query Low
- Query High
- Archive Low
- Archive High

Algorithms:
LZO: Faster compression speeds, but lower ratios (Query Low)
ZLIB: Decent speeds and good ratios (Query High and Archive Low)
BZ2: Slow, but the highest ratios (Archive High)

4. Exadata storage index:

Transparent I/O Elimination with No Overhead.

- Exadata storage indexes maintain summary information about table data in memory.
* Store MIN and MAX values of columns.
* Typically one index entry for every MB of disk.
- Eliminates disk I/O if MIN and MAX can never match "WHERE" clause of a query.

5. DBFS (database file system) - Rest of the 10 SYSTEMDB disks can be used as DBFS.

6. IORM (I/O resource manager - Exadata specific)

Monitoring Exadata:

- Install GC agent on each compute node.
- No GC agent on cell nodes.
- Agent on one compute node talks with all cell nodes and collects data.
- Grid control plugin is installed in OMS.

Oracle masala at work - 3:

2010-06-22T17:29:00.002-04:00

Oracle masala at work - 3:

1. Restore and recovery made easy with RMAN Data Recovery Advisor in Oracle 11g:
2. Restart Data Pump job after an error:
3. NFS mount options on Linux(RHEL) for expdp and impdp:
4. Useful Underutilized some Oracle Utilities:

1. Restore and recovery made easy with RMAN Data Recovery Advisor in Oracle 11g:

rman target /

-- If there is an error, this command will come back with the files to recover.
list failure;

-- This command will show you the exact cause of the error. 169 is failure ID from the "list failure;" command.
list failure 169 detail;

-- It responds with a detailed explanation of the error and how to correct it.
advise failure;

-- It responds with a detailed fix preview.
repair failure preview;

-- This command will fix the failure.
repair failure;

-- The following RMAN command recovers all corrupted blocks.
recover corruption list;

2. Restart Data Pump job after an error:

-- In order to find the System assigned name for the EXPDP/IMPDP job you can run the following query.
SELECT * FROM DBA_DATAPUMP_JOBS;

-- Attach the job from the above SQL.
$ impdp system/manager attach=job_name

-- Restart the job.
Import> start_job

-- Check the status of the job.
Import> status

3. NFS mount options on Linux(RHEL) for expdp and impdp:

mount options:
atlt200:/oracle_scratch on /oracle_scratchnfs type nfs (rw,rsize=32768,wsize=32768,hard,nointr,bg,nfsvers=3,tcp,actimeo=0,timeo=600,addr=xx.xx.xxx.xx)

uname -a: Linux atlt200 2.6.9-78.ELsmp #1 SMP Wed Jul 9 15:46:26 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux
Server: Dell 2950

4. Useful Underutilized some Oracle Utilities:

bbed (Block Browser and Editor, Password is "blockedit")
oradebug
nid (Rename DBID and Database Name)
adrci (11g)
trcsess (trcsess utility allows trace information from multiple trace files to be identified and consolidated into a single trace file from 10g)
trace Analyzer (trcanlzr.sql - Trace Analyzer utility is available via download on the Oracle Metalink web site)
trcasst (Analyxing Listener Trace file)
deinstall (11gR2 - Deinstall Failed GRID or Oracle home’s - File is under $ORACLE_HOME/deinstall directory)
csscan (Character Set Migration Utility - $ csscan \"sys/keepsaf3 as sysdba\" FULL=Y, to create character set migration utility schema, run @?/rdbms/admincsminst.sql)
wrap (hide the PL/SQL code)
cemutlo (cemutlo -n gives the cluster name)

renamedg (11gR2 ASM - Rename the ASM diskgroup)
kfed (ASM - display the diskgroup information)

Tools:
CHM (formerly know as IPD/OS) - Oracle Cluster Health Monitor.
ORION - Oracle I/O Calibration Tool - Load test tool.

Renaming ASM Disk group in Oracle 11gR2:

2010-06-22T16:46:00.001-04:00

Renaming ASM Disk group in Oracle 11gR2:

The renamedg utility is new in Oracle 11gR2 and it's documented in the storage administrator’s guide.
You can use this tool to rename ASM diskgroups. The prerequisite is to unmount the disk group on all cluster nodes.
The tool works in 2 phases, in the first phase it generates a configuration file and in the second phase it discovers the disks and rename the disk group.

How to use renamedg command to rename the ASM diskgroup?

The following example demonstrates how to rename the ASM diskgroup from A_DATA to B_DATA.

a. Setup the environment:

$ . oraenv
+ASM1

$ export PATH=$PATH:/dev/oracleasm/disks
$ echo $PATH

b. Dismount the ASM diskgroup to be renamed on all cluster nodes:

$ asmcmd umount A_DATA -- on all nodes.

c. Verify the diskgroup was dismounted:

$ crsctl status resource ora.A_DATA.dg

NAME=ora.A_DATA.dg
TYPE=ora.diskgroup.type
TARGET=OFFLINE, OFFLINE
STATE=OFFLINE, OFFLINE

d. Using renamedg utility to rename the diskgroup:

$ renamedg phase=both dgname=A_DATA newdgname=B_DATA verbose=true

Output:
----***********----
Parsing parameters..

Parameters in effect:

Old DG name : A_DATA
New DG name : B_DATA
Phases :
Phase 1
Phase 2
Discovery str : (null)
Clean : TRUE
Raw only : TRUE
renamedg operation: phase=both dgname=A_DATA newdgname=B_DATA verbose=true
Executing phase 1
Discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0681 with disk number:0 and timestamp (32937868 1717605376)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0685 with disk number:1 and timestamp (32937868 1717605376)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0681 with disk number:0 and timestamp (32937868 1717605376)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0685 with disk number:1 and timestamp (32937868 1717605376)
Checking if the diskgroup is mounted
Checking disk number:0
Checking disk number:1
Checking if diskgroup is used by CSS
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for ORCL:S_1873_0681
Modifying the header
Looking for ORCL:S_1873_0685
Modifying the header
Completed phase 2
Terminating kgfd context 0x2b06e27d20a0
----***********----

e. Mounting the ASM diskgroup:

$ asmcmd mount B_DATA -- on all nodes.

f. Check if the diskgroup was renamed and mounted successfully:

$ crsctl status resource ora.B_DATA.dg

NAME=ora.B_DATA.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE , ONLINE
STATE=ONLINE on atld380, ONLINE on atld381

Yaaa, diskgroup has been renamed from A_DATA to B_DATA successfully.

Possible error when issuing renamedg command:

Error: "KFNDG-00408: disk (string:string) could not be discovered error"
Solution: Add the ASM disk path to $PATH environment variable "export PATH=$PATH:/dev/oracleasm/disks"

Output:
----***********----
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0638 with disk number:0 and timestamp (32937868 354065408)
Checking if the diskgroup is mounted
Checking disk number:0
Checking if diskgroup is used by CSS
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for ORCL:S_1873_0638
ERROR: -9(Error 7217, OS Error ()
)KFNDG-00408: file not found; arguments: [] [ORCL:S_1873_0638]

Terminating kgfd context 0x2b3ddb1d80a0
----***********----

The renamedg command usage:

$ renamedg -help

Parsing parameters..
phase Phase to execute (phase=ONE|TWO|BOTH), default BOTH

dgname Diskgroup to be renamed

newdgname New name for the diskgroup

config intermediate config file

check just check-do not perform actual operation,
(check=TRUE/FALSE), default FALSE

confirm confirm before committing changes to disks,
(confirm=TRUE/FALSE), default FALSE

clean ignore errors (clean=TRUE/FALSE), default TRUE

asm_diskstring ASM Diskstring (asm_diskstring='discoverystring',
'discoverystring1' ...)

verbose verbose execution (verbose=TRUE|FALSE), default
FALSE

keep_voting_files Voting file attribute,
(keep_voting_files=TRUE|FALSE), default FALSE

Q:
a.
Do we need to modify the asm_diskgroups initialization parameter after renaming the disk groups?

No, you don’t have to. The renamedg utility is smart enough to update the asm_diskgroups initialization parameter.

b.
How do I rename the ASM disk group with datafile in it?

After rename the disk group, start the database in MOUNT mode and rename the datafiles using "ALTER DATABASE RENAME FILE . . . TO . . ." command to OPEN the database.

c.
Does it mater how many disks in the disk group?

No, tried with 2 disks DG and also with 8 disks DG.

Compare and Generate the Object Metadata(Structure) Difference in Oracle 11gR2:

2010-06-05T21:48:00.003-04:00

Compare and Generate the Object Metadata(Structure) Difference in Oracle 11gR2:

The DBMS_METADATA_DIFF package provides interfaces to compare and generate database objects structure difference between databases.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
X TABLE
Y TABLE

SQL> desc x
Name Null? Type
----------------------------------------- -------- -------------
ENO NOT NULL NUMBER(10)
ENAME VARCHAR2(20)
SAL NUMBER(10)

SQL> desc y
Name Null? Type
----------------------------------------- -------- -------------
ENO NUMBER(10)
ENAME VARCHAR2(20)

set heading off;
set echo off;
set pages 999;
set long 90000;

1. Compare the tables in same schema:

SQL> show user
USER is "TEST"
SQL> select dbms_metadata_diff.compare_alter('TABLE','X','Y','TEST','TEST') from dual;

ALTER TABLE "TEST"."X" DROP ("SAL")
ALTER TABLE "TEST"."X" DROP CONSTRAINT "X_PK"
ALTER TABLE "TEST"."X" RENAME TO "Y"

SQL> select dbms_metadata_diff.compare_alter('TABLE','Y','X','TEST','TEST') from dual;

ALTER TABLE "TEST"."Y" ADD ("SAL" NUMBER(10,0))
ALTER TABLE "TEST"."Y" ADD CONSTRAINT "X_PK" PRIMARY KEY ("ENO") USING INDEX P
CTFREE 10 INITRANS 2 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENT
S 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLA
SH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) ENABLE
ALTER TABLE "TEST"."Y" RENAME TO "X"

SQL>

2. Compare the tables in different schemas in same database:

SQL> show user
USER is "SYSTEM"
SQL> select dbms_metadata_diff.compare_alter('TABLE','Y','X','TEST','TEST2') from dual;

ALTER TABLE "TEST"."Y" ADD ("SAL" NUMBER(10,0))
ALTER TABLE "TEST"."Y" ADD CONSTRAINT "X_PK" PRIMARY KEY ("ENO") USING INDEX P
CTFREE 10 INITRANS 2 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENT
S 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLA
SH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) ENABLE
ALTER TABLE "TEST"."Y" RENAME TO "X"

SQL>

3. Compare the tables in different databases:
-- Assume we are comparing the object between Test and Stage databases and the database link is created between Stage and Test.
-- Stage DB: SODS
-- Test DB: TODS
-- Database Link from Stage to Test: TODS.WORLD

TODS:
SQL> desc x
Name Null? Type
----------------------------------------- -------- -------------
ENO NOT NULL NUMBER(10)
ENAME VARCHAR2(20)
SAL NUMBER(10)

SODS:
SQL> desc x
Name Null? Type
----------------------------------------- -------- -------------
ENO NUMBER(10)
ENAME VARCHAR2(20)

SQL> show user
USER is "TEST"
SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
SODS.WORLD

SQL> select dbms_metadata_diff.compare_alter('TABLE','X','X','TEST','TEST',NULL,'TODS.WORLD') from dual;

ALTER TABLE "TEST"."Y" ADD ("SAL" NUMBER(10,0))
ALTER TABLE "TEST"."Y" ADD CONSTRAINT "X_PK" PRIMARY KEY ("ENO") USING INDEX P
CTFREE 10 INITRANS 2 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENT
S 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLA
SH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) ENABLE
ALTER TABLE "TEST"."Y" RENAME TO "X"

SQL>

Notice: Use of the DBMS_METADATA_DIFF package requires the Oracle Enterprise Manager Change Manager license.

Compare and Fix the Object Data in Oracle 11g:

2010-05-25T21:12:00.005-04:00

Compare and Fix the Object Data in Oracle 11g:

The DBMS_COMPARISON package provides interfaces to compare and sync database objects at different databases. The index columns in a comparison must uniquely identify every row involved in a comparison.

The following constraints satisfy this requirement:

- A primary key constraint
- A unique constraint on one or more non-NULL columns

If these constraints are not present on a table, then use the index_schema_name and index_name parameters in the CREATE_COMPARISON procedure to specify an index whose columns satisfy this requirement.

Simple Test:
drop table test.x PURGE;
drop table test2.x PURGE;

create table test.x (eno number(10), ename varchar2(20), sal number(10));
create table test2.x (eno number(10), ename varchar2(20), sal number(10));

alter table test.x add constraint x_pk primary key (eno);
alter table test2.x add constraint x_pk primary key (eno);

insert into test.x values (100, 'VJ', 100000);
insert into test.x values (101, 'ASHRAY', 100001);
insert into test.x values (102, 'DUMPA', 200000);
insert into test.x values (103, 'REDDY', 300000);
commit;

insert into test2.x values (100, 'VJ', 100000);
insert into test2.x values (101, 'ASHRAY', 100000);
insert into test2.x values (102, 'DUMPA', 200001);
insert into test2.x values (103, 'REDDY', 300000);
commit;

select * from test.x;
select * from test2.x;

-- Drop the existing comparison:

SELECT comparison_name, schema_name FROM dba_comparison;

BEGIN
dbms_comparison.drop_comparison (comparison_name => 'comp1');
END;
/

-- Create the comparison:
-- dblink_name is NULL because both schemas on the same database.

BEGIN
dbms_comparison.create_comparison (comparison_name => 'comp1',
schema_name => 'test',
object_name => 'x',
dblink_name => NULL,
remote_schema_name => 'test2',
remote_object_name => 'x'
);
END;
/

-- Check the difference "YES/NO":

set serveroutput on size 99999;

DECLARE
CONSISTENT BOOLEAN;
scan_info dbms_comparison.comparison_type;
BEGIN
CONSISTENT :=
dbms_comparison.compare (comparison_name => 'comp1',
scan_info => scan_info,
perform_row_dif => TRUE
);
DBMS_OUTPUT.put_line ('Scan ID: ' || scan_info.scan_id);

IF CONSISTENT = TRUE
THEN
DBMS_OUTPUT.put_line ('No differences were found.');
ELSE
DBMS_OUTPUT.put_line ('Differences were found.');
END IF;
END;
/
Scan ID: 11
Differences were found.

PL/SQL procedure successfully completed.

-- Comparison Summary:
-- Where scan_id is from the above dbms_comparison.compare PL/SQL block.

set linesize 120;
COL schema_name for a20;
COL object_name for a20;
COL comparison_name for a20;

SELECT s.scan_id, c.comparison_name, c.schema_name, c.object_name,
s.current_dif_count
FROM user_comparison c, user_comparison_scan_summary s
WHERE c.comparison_name = s.comparison_name AND s.scan_id = 11;

-- Comparison Details:

set linesize 120;
COL record_value for a50;
COL local_rowid for a12;
COL remote_rowid format a12;

SELECT c.column_name, r.index_value record_value,
CASE
WHEN r.local_rowid IS NULL
THEN 'No'
ELSE 'Yes'
END local_rowid,
CASE
WHEN r.remote_rowid IS NULL
THEN 'No'
ELSE 'Yes'
END remote_rowid
FROM dba_comparison_columns c,
dba_comparison_row_dif r,
dba_comparison_scan s
WHERE c.comparison_name = 'COMP1'
AND r.scan_id = s.scan_id
-- AND s.last_update_time > SYSTIMESTAMP - 1 / 24 / 12 -- Last 5 min.
AND r.status = 'DIF'
AND c.index_column = 'Y'
AND c.comparison_name = r.comparison_name
ORDER BY r.index_value;

-- Fix the difference in the remote table:

DECLARE
scan_info dbms_comparison.comparison_type;
BEGIN
dbms_comparison.converge
(comparison_name => 'comp1',
scan_id => 11,
scan_info => scan_info,
converge_options => dbms_comparison.cmp_converge_local_wins
);
DBMS_OUTPUT.put_line ('Local Rows Merged: ' || scan_info.loc_rows_merged);
DBMS_OUTPUT.put_line ('Remote Rows Merged: ' || scan_info.rmt_rows_merged);
DBMS_OUTPUT.put_line ('Local Rows Deleted: ' || scan_info.loc_rows_deleted);
DBMS_OUTPUT.put_line ('Remote Rows Deleted: ' || scan_info.rmt_rows_deleted);
END;
/
Local Rows Merged: 0
Remote Rows Merged: 2
Local Rows Deleted: 0
Remote Rows Deleted: 0

PL/SQL procedure successfully completed.

Results:

Before:
select * from test.x;
select * from test2.x;

SQL> select * from test.x;

ENO ENAME SAL
---------- -------------------- ----------
100 VJ 100000
101 ASHRAY 100001
102 DUMPA 200000
103 REDDY 300000

SQL> select * from test2.x;

ENO ENAME SAL
---------- -------------------- ----------
100 VJ 100000
101 ASHRAY 100000 <=== Old
102 DUMPA 200001 <=== Old
103 REDDY 300000

After:
select * from test.x;
select * from test2.x;

SQL> select * from test.x;

ENO ENAME SAL
---------- -------------------- ----------
100 VJ 100000
101 ASHRAY 100001
102 DUMPA 200000
103 REDDY 300000

SQL> select * from test2.x;

ENO ENAME SAL
---------- -------------------- ----------
100 VJ 100000
101 ASHRAY 100001 <=== New
102 DUMPA 200000 <=== New
103 REDDY 300000

SQL>

Q:
a.
Can I fix the CLOB/BLOB column data?

No, the DBMS_COMPARISON package cannot compare data in columns of the following data types:

- LONG, LONG RAW, ROWID, UROWID, CLOB, NCLOB, BLOB and BFILE
- User-defined types (including object types, REFs, varrays, and nested tables)
- Oracle-supplied types (including any types, XML types, spatial types, and media types)

b.
Can I fix the object data in 11g by comparing the object in 10g?

Yes, The local database that runs the DBMS_COMPARISON package must be 11g but the remote database must be 10gR1 and up.

c.
Can I compare packages/procedure/functions?

No, the DBMS_COMPARISON package can compare the following types of database objects:

Tables, Single-table views, Materialized views, Synonyms for tables, single-table views and materialized views.

d.
Do I need to have primary key on compare object?

No, but each column in the single-column/composite index must either have a NOT NULL constraint or be part of the primary key.

e.
What privileges do I need to compare the objects?

Granting EXECUTE on CREATE_COMPARISON package to selected users or roles. (or) Granting EXECUTE_CATALOG_ROLE to selected users or roles.

No Segment Vs Invisible Vs Unusable Indexes in Oracle 11gR2:

2010-04-20T21:10:00.006-04:00

No Segment Vs Invisible Vs Unusable Indexes in Oracle 11gR2:

No Segment/Virtual Indexes (8i and up):

Virtual indexes allow us to simulate the existence of an index and test its impact without actually building the actual index.
Only sessions marked for Virtual Index usage will be affected by their existence. Their creation does not affect new sessions.
Virtual indexes will be used only when the initialization parameter "_use_nosegment_indexes" is set to TRUE.
The Rule based optimizer does not recognize Virtual Indexes but the CBO does recognize them.
Dictionary view DBA_SEGMENTS will not show entries for Virtual Indexes. [DBA|ALL|USER]_OBJECTS view will have an entry.
They are permanent and continue to exist unless dropped. Make sure to drop virtual indexes after analysis and tuning is completed.
Statistics can be gathered on virtual indexes in the same way as regular indexes.
Oracle will prevent us from creating another virtual index with the same column list, but it will allow us to create a real index with the same column list.

To detect a virtual index in the database run the following SQL (these indexes don't have any columns in dba_ind_columns):

SELECT index_owner, index_name
FROM dba_ind_columns
WHERE index_name NOT LIKE 'BIN$%'
MINUS
SELECT owner, index_name
FROM dba_indexes;

Invisible Indexes (11gR1 and up):

Beginning with Release 11g, you can create invisible indexes. An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level.

ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;
ALTER SYSTEM SET optimizer_use_invisible_indexes=TRUE;

Using invisible indexes, you can do the following:
- Test the optimizer behave on the application before dropping an index.
- Use the index for certain operations or modules of an application without affecting the overall performance of the application.
- It's also useful for some DELETE operations in Database Machine (Oracle EXADATA).

An invisible index is maintained during DML statements.
Statistics can be gathered on an invisible indexes.
The current visibility status of an index is indicated by the VISIBILITY column of the [DBA|ALL|USER]_INDEXES views.
Using ALTER INDEX we can make the index INVISIBLE or VISIBLE.
We can REBUILD an invisible index.

INDEX hit will not work with an invisible index.
/*+ opt_param('optimizer_use_invisible_indexes','TRUE') */ -- we CAN'T use opt_param hint
We CAN'T modify index partition to Invisible, it's at Index level.

Unusable Indexes (11gR2):

Zero Sized Unusable Indexes:
In Oracle 11gR2 when the index or index partition marked as unusable, oracle automatically drop any index segment space.
This means we can release OLD(Not Active) index partition space and we can keep NEW(Active) partition indexes.

ALTER INDEX MODIFY PARTITION UNSABLE;

Once we make an index unusable, we will not see the segment in [DBA|ALL|USER]_SEGMENTS view, but we will have entry in [DBA|ALL|USER]_IND_PARTITIONS view.
The column SEGMENT_CREATED(column value: YES/NO) shows whether a segment exists for that partition.
unlike previous oracle releases(< 11gR2), we can query the unusable partition data, but it uses full table scan instead of an index scan, because the corresponding index partition is unusable and cannot be used by the optimizer.
When you query against NEW(Active) partition, it uses the corresponding index scan.
If a table is truncated, the unusable index partition will become usable again and Oracle Database will re-create the segment.

OEM Grid Control Maintenance Tasks - Weekly/Monthly:

2009-09-26T16:29:00.005-04:00

OEM Grid Control Maintenance Tasks - Weekly/Monthly:

OMS Monthly Maintenance Tasks:

1. OEM database partition Maintenance:

a. Shut down all OMSs:
$OMS_HOME/bin/emctl stop oms

b. Connect as SYSMAN to the OEM database and run following two procedures:
SQL>connect sysman/xxxx
SQL>set timing on;
SQL>exec emd_maintenance.analyze_end_schema('SYSMAN');
SQL>commit;
SQL>exec emd_maintenance.partition_maintenance;
SQL>commit;

If you are in a scenario where you hit Bug 5357916 and can't apply the patch or upgrade to a version where the patch is available you need to do the following:

SQL>connect / as sysdba
SQL>alter system set job_queue_processes = 0;
SQL>connect sysman/xxxx
SQL>set timing on;
SQL>exec emd_maintenance.remove_em_dbms_jobs;
SQL>exec emd_maintenance.partition_maintenance;

SQL>@/sysman/admin/emdrep/sql/core/latest/admin/admin_recompile_invalid.sql
SQL>alter system set job_queue_processes = 10;
SQL>exec emd_maintenance.submit_em_dbms_jobs;
SQL>commit;

Note: The partition maintenance will be performed automatically if you have the following configuration:
Grid Control 10gR3 (10.2.0.3) or later with the repository installed in a 10.2.0.2 database or later.

c. Confirm the Partition maintenance was successful:
select count(1) from sysman.mgmt_metrics_raw where collection_timestamp < sysdate -9;

d. Restart all OMSs:
$OMS_HOME/bin/emctl start oms

2. Rebuild/Shrink tables and indexes as required:

OMS Weekly Maintenance Tasks:

1. Check and Clear OMS and OEM database system errors.

a. OMS:
Goto: Navigate in the Grid Console to Setup >> Management Services and Repository >> 'Errors' Tab

b. OEM Database alertlog errors:
Goto: $ORACLE_BASE/POEM/bdump/alert_POEM[12].log

2. Fix target metric collection errors.

a. Cluster agent:
$AGENT_HOME/bin/agentca -d -c catlmsxt261 -- Discover
$AGENT_HOME/bin/agentca -f -c catlmsxt261 -- Reconfigure
$AGENT_HOME/bin/emctl clearstate agent
$AGENT_HOME/bin/emctl status agent

b. Standalone agent:
$AGENT_HOME/bin/agentca -f
$AGENT_HOME/bin/emctl clearstate agent
$AGENT_HOME/bin/emctl status agent

3. Start or remove targets in DOWN status and also remove all the Duplicate Targets.

4. Force delete all the "Deleted Targets"
Goto: Navigate in the Grid Console to Setup >> Management Services and Repository >> 'Overview' Tab >> Deleted Targets list
(or DELETE FROM SYSMAN.MGMT_TARGETS_DELETE; COMMIT ;)

5. Clear Critical and Warning alerts:
Goto: Alerts tab on the OEM main page.

6. Analyze Metric Rollup Tables as required:

MGMT_METRICS_RAW
MGMT_METRICS_1DAY
MGMT_METRICS_1HOUR

exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_RAW', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);
exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_1HOUR', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);
exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_1DAY', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);

Reference:
Doc ID: 456101.1
Doc ID: 370695.1

How to automate partition (Range - Seconds to Years) creation using Oracle Interval Partitioning in 11g:

2009-09-21T17:17:00.002-04:00

How to automate partition (Range - Seconds to Years) creation using Oracle Interval Partitioning in 11g:

1. Create a composite Interval-List/Interval-Range partition table:

drop table x;
create table x (create_date date, site_id number, all_data varchar2(100))
PARTITION BY RANGE (create_date)
INTERVAL (NUMTODSINTERVAL(1,'HOUR'))
SUBPARTITION BY LIST (site_id)
(
PARTITION part_01 values LESS THAN (TO_DATE('18-SEP-2009 16:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_01_01 VALUES (01),
SUBPARTITION part_01_126 VALUES (126),
SUBPARTITION part_01_132 VALUES (132),
SUBPARTITION part_01_135 VALUES (135)
),
PARTITION part_02 values LESS THAN (TO_DATE('18-SEP-2009 17:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_02_01 VALUES (01),
SUBPARTITION part_02_126 VALUES (126),
SUBPARTITION part_02_132 VALUES (132),
SUBPARTITION part_02_135 VALUES (135)
),
PARTITION part_03 values LESS THAN (TO_DATE('18-SEP-2009 18:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_03_01 VALUES (01),
SUBPARTITION part_03_126 VALUES (126),
SUBPARTITION part_03_132 VALUES (132),
SUBPARTITION part_03_135 VALUES (135)
),
PARTITION part_04 values LESS THAN (TO_DATE('18-SEP-2009 19:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_04_01 VALUES (01),
SUBPARTITION part_04_126 VALUES (126),
SUBPARTITION part_04_132 VALUES (132),
SUBPARTITION part_04_135 VALUES (135)
),
PARTITION part_05 values LESS THAN (TO_DATE('18-SEP-2009 20:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_05_01 VALUES (01),
SUBPARTITION part_05_126 VALUES (126),
SUBPARTITION part_05_132 VALUES (132),
SUBPARTITION part_05_135 VALUES (135)
),
PARTITION part_06 values LESS THAN (TO_DATE('18-SEP-2009 21:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_06_01 VALUES (01),
SUBPARTITION part_06_126 VALUES (126),
SUBPARTITION part_06_132 VALUES (132),
SUBPARTITION part_06_135 VALUES (135)
),
PARTITION part_07 values LESS THAN (TO_DATE('18-SEP-2009 22:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_07_01 VALUES (01),
SUBPARTITION part_07_126 VALUES (126),
SUBPARTITION part_07_132 VALUES (132),
SUBPARTITION part_07_135 VALUES (135)
),
PARTITION part_08 values LESS THAN (TO_DATE('18-SEP-2009 23:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_08_01 VALUES (01),
SUBPARTITION part_08_126 VALUES (126),
SUBPARTITION part_08_132 VALUES (132),
SUBPARTITION part_08_135 VALUES (135)
)
);

2. Create bitmap LOCAL indexes:

create bitmap index idx1_x on x(create_date) local;
create bitmap index idx2_x on x(site_id) local;

3. Insert and Check the data in each partition:

truncate table x;
insert into x values(TO_DATE('18-SEP-2009 15:30:00','DD-MON-YYYY HH24:MI:SS'),01,'18/SEP/09 of 15''Th HR - SITE 01');
insert into x values(TO_DATE('18-SEP-2009 16:30:00','DD-MON-YYYY HH24:MI:SS'),126,'18/SEP/09 of 16''Th HR - SITE 126');
insert into x values(TO_DATE('18-SEP-2009 17:30:00','DD-MON-YYYY HH24:MI:SS'),132,'18/SEP/09 of 17''Th HR - SITE 132');
insert into x values(TO_DATE('18-SEP-2009 18:30:00','DD-MON-YYYY HH24:MI:SS'),135,'18/SEP/09 of 18''Th HR - SITE 125');
insert into x values(TO_DATE('18-SEP-2009 19:30:00','DD-MON-YYYY HH24:MI:SS'),01,'18/SEP/09 of 19''Th HR - SITE 01');
insert into x values(TO_DATE('18-SEP-2009 20:30:00','DD-MON-YYYY HH24:MI:SS'),126,'18/SEP/09 of 20''Th HR - SITE 126');
insert into x values(TO_DATE('18-SEP-2009 21:30:00','DD-MON-YYYY HH24:MI:SS'),132,'18/SEP/09 of 21''Th HR - SITE 132');
insert into x values(TO_DATE('18-SEP-2009 22:30:00','DD-MON-YYYY HH24:MI:SS'),135,'18/SEP/09 of 22''Th HR - SITE 135');
commit;

set linesize 200;
col all_data for a50;

SELECT * FROM x PARTITION (PART_01);
SELECT * FROM x PARTITION (PART_02);
SELECT * FROM x PARTITION (PART_03);
SELECT * FROM x PARTITION (PART_04);
SELECT * FROM x PARTITION (PART_05);
SELECT * FROM x PARTITION (PART_06);
SELECT * FROM x PARTITION (PART_07);
SELECT * FROM x PARTITION (PART_08);

4. Check and record the HIGH VALUE of the partition table:

col table_name for a10;
col partition_name for a15;
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

8 rows selected.

5. Check and record the HIGH VALUE of the Subpartition table:

col subpartition_name for a20;
col high_value for a10;
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
X PART_01 PART_01_135 135
X PART_01 PART_01_132 132
X PART_01 PART_01_126 126
X PART_01 PART_01_01 01
X PART_02 PART_02_135 135
X PART_02 PART_02_132 132
X PART_02 PART_02_126 126
X PART_02 PART_02_01 01
X PART_03 PART_03_135 135
X PART_03 PART_03_132 132
X PART_03 PART_03_126 126
X PART_03 PART_03_01 01
X PART_04 PART_04_135 135
X PART_04 PART_04_132 132
X PART_04 PART_04_126 126
X PART_04 PART_04_01 01
X PART_05 PART_05_135 135
X PART_05 PART_05_132 132
X PART_05 PART_05_126 126
X PART_05 PART_05_01 01
X PART_06 PART_06_135 135
X PART_06 PART_06_132 132
X PART_06 PART_06_126 126
X PART_06 PART_06_01 01
X PART_07 PART_07_135 135
X PART_07 PART_07_132 132
X PART_07 PART_07_126 126
X PART_07 PART_07_01 01
X PART_08 PART_08_135 135
X PART_08 PART_08_132 132
X PART_08 PART_08_126 126
X PART_08 PART_08_01 01

32 rows selected.

6. Insert new record (Next day 15'th Hour) and validate the partition creation:

insert into x values(TO_DATE('19-SEP-2009 15:30:00','DD-MON-YYYY HH24:MI:SS'),01,'19/SEP/09 of 15''Th HR - SITE 01');
commit;

col high_value for a10
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
..
..
..

X PART_08 PART_08_01 01
X SYS_P42 SYS_SUBP41 DEFAULT <------- New Partition

33 rows selected.

col high_value for a80
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P42 TO_DATE(' 2009-09-19 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA <------- New Partition

9 rows selected.

7. Insert new record(Next day 17'th Hour) and validate the partition creation:

insert into x values(TO_DATE('19-SEP-2009 17:30:00','DD-MON-YYYY HH24:MI:SS'),132,'19/SEP/09 of 17''Th HR - SITE 132');
commit;

col high_value for a10
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
..
..
..
X PART_08 PART_08_01 01
X SYS_P42 SYS_SUBP41 DEFAULT
X SYS_P44 SYS_SUBP43 DEFAULT <------- New Partition

34 rows selected.

col high_value for a80
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P42 TO_DATE(' 2009-09-19 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P44 TO_DATE(' 2009-09-19 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA <------- New Partition

10 rows selected.

8. Insert new record (Next day 16'th Hour) and validate the partition creation:
# This record data is one hour less than the previous record in Step 7.
# No problem with creating the partition even with jumbled hourly data!!!!

insert into x values(TO_DATE('19-SEP-2009 16:30:00','DD-MON-YYYY HH24:MI:SS'),126,'19/SEP/09 of 16''Th HR - SITE 126');
commit;

col high_value for a10
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
..
..
..
X PART_08 PART_08_01 01
X SYS_P42 SYS_SUBP41 DEFAULT
X SYS_P44 SYS_SUBP43 DEFAULT
X SYS_P46 SYS_SUBP45 DEFAULT <------- New Partition

35 rows selected.

col high_value for a80
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P42 TO_DATE(' 2009-09-19 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P44 TO_DATE(' 2009-09-19 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P46 TO_DATE(' 2009-09-19 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA <------- New Partition

11 rows selected.

Side Note:
If you want to create different intervals, here are the available options:

NUMTODSINTERVAL - An Interval Day to Second:

'DAY' - INTERVAL (NUMTODSINTERVAL(1,'DAY')) or INTERVAL (NUMTODSINTERVAL(7,'DAY'))
'HOUR' - INTERVAL (NUMTODSINTERVAL(1,'HOUR')) or INTERVAL (NUMTODSINTERVAL(8,'HOUR'))
'MINUTE' - INTERVAL (NUMTODSINTERVAL(1,'MINUTE')) or INTERVAL (NUMTODSINTERVAL(15,'MINUTE'))
'SECOND' - INTERVAL (NUMTODSINTERVAL(1,'SECOND')) or INTERVAL (NUMTODSINTERVAL(30,'SECOND'))

NUMTOYMINTERVAL - An Interval Year to Month:

'YEAR' - INTERVAL (NUMTOYMINTERVAL(1,'YEAR')) or INTERVAL (NUMTOYMINTERVAL(3,'YEAR'))
'MONTH' - INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) or INTERVAL (NUMTOYMINTERVAL(4,'MONTH'))

How to Restore Database BCV backup copy to ASM storage:

2009-04-17T14:03:00.005-04:00

How to Restore Database BCV backup copy to ASM storage:

How big is the database: 2TB
How often we take BCV copy: Daily
How often we delete Archive log's: Every 30 mns.
How much redo, does this DB generate per Hour: 70GB/24= ~3GB
Is this RAC database: Yes.

1.
Stop all instances of database:
srvctl stop database -d ORADB

2.
Check the services are offline:
crs_stat -t

3.
Dismount diskgroups on all instances:

alter diskgroup ORADB_T1_BACKUP_01 dismount;
alter diskgroup ORADB_T1_DATA_01 dismount;
alter diskgroup ORADB_T1_DATA_02 dismount;
alter diskgroup ORADB_T1_DATA_03 dismount;
alter diskgroup ORADB_T1_DATA_04 dismount;
alter diskgroup ORADB_T1_DATA_05 dismount;
alter diskgroup ORADB_T1_REDO_01 dismount;

4.
Have SysAdmin restore the database from BCV:

Is there any difference in restoring BCV copy to file system Vs ASM?
There is technically no difference in restoring BCV copy to file system Vs ASM.

Can ASM disk names be different?
No, should be same between source and target.

5.
Mount back diskgroups on all instances:

alter diskgroup ORADB_T1_BACKUP_01 mount;
alter diskgroup ORADB_T1_DATA_01 mount;
alter diskgroup ORADB_T1_DATA_02 mount;
alter diskgroup ORADB_T1_DATA_03 mount;
alter diskgroup ORADB_T1_DATA_04 mount;
alter diskgroup ORADB_T1_DATA_05 mount;
alter diskgroup ORADB_T1_REDO_01 mount;

6.
Startup and Recover the database: -- Source to DB instance

SQL> startup;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2084296 bytes
Variable Size 419430968 bytes
Database Buffers 1174405120 bytes
Redo Buffers 14692352 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'+ORADB_T1_DATA_01/oradb/datafile/system.256.667735945'

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

7.
Restart all services/instances:
crs_start -all

8.
Check the services are online:
crs_stat -t

How to swinging the RAC(ASM) database from one cluster to another cluster:

2009-03-23T16:10:00.001-04:00

How to swinging the RAC(ASM) database from one cluster to another cluster:

1. Un-mount all the diskgroups from the source RAC cluster on all the instances.

ALTER DISKGROUP ALL DISMOUNT; or diskgroups specific to the DB

2. Shutdown all the instances(database).

srvctl stop database -d DB_NAME

3. Copy DB init.ora file to all the RAC nodes.

4. Swinging the disks from one cluster to other cluster. (Storage and Sys Admin Task).

High Level: After ASM(un-mount) and DB are stopped, SAN team un-zone the storage from source RAC cluster and zone it to destination RAC cluster.
And perform the ASM scan disk (/etc/init.d/oracleasm scandisks) to see all the disks in destination ASM instance.

5. Mount all the diskgroups on the destination RAC cluster on all the instances.

ALTER DISKGROUP ALL MOUNT;

6. Register all the instances and database.

srvctl add instance -d DB_NAME -i INSTANCE1 -n NODE1
srvctl add instance -d DB_NAME -i INSTANCE2 -n NODE2
srvctl add instance -d DB_NAME -i INSTANCE3 -n NODE3
srvctl add instance -d DB_NAME -i INSTANCE4 -n NODE4

srvctl add database -d DB_NAME -o $ORACLE_HOME
srvctl modify instance -d DB_NAME -i INSTANCE_NAME -s ASM_INSTANCE_NAME

7. Startup all the instances(database).

srvctl start database -d DB_NAME
or crs_start -all

8. Create all the services using dbca. Incase of any issues with dbca use below command.

srvctl add service -d DB_NAME -s SERVICE_NAME -r INSTANCE1,INSTANCE2,INSTANCE3 -a INSTANCE4 -P BASIC

-r for preferred nodes
-a for available nodes
-P for TAF Policy (NONE, BASIC or PRECONNECT)

9. Update /etc/oratab, tnsnames.ora on all the servers and S drive.

10. Update all the new diskgroups in ASM init.ora file on the destination RAC cluster on all the nodes.

11. Cleanup the database and instances CRS resources from the source RAC cluster.

srvctl remove instance -d DB_NAME -i INSTANCE1
srvctl remove instance -d DB_NAME -i INSTANCE2
srvctl remove instance -d DB_NAME -i INSTANCE3
srvctl remove instance -d DB_NAME -i INSTANCE4

srvctl remove database -d DB_NAME

srvctl remove service -d DB_NAME -s SERVICE_NAME

How to take RMAN Backup from BCV copy of the Database:

2009-02-24T11:01:00.003-05:00

How to take RMAN Backup from BCV copy of the Database:

1.
a.
DB Release info of target DB, catalog DB, BCV copy DB:
a) 10.2.0.4 - ASM storage
b) 10.2.0.4
c) 10.2.0.4 - ASM storage

b..
OS Release info of Primary and BCV:
a) Red Hat Enterprise Linux AS release 4 (Nahant Update 4)
b) Red Hat Enterprise Linux AS release 4 (Nahant Update 4)

2. Please verify fhrba_seq column values, should match between Primary and BCV copy:

-- On Primary
set linesize 120;
select hxfil FILE#,fhsta STAT,fhscn SCN, fhthr thr, fhrba_Seq SEQUENCE,fhtnm TABLESPACE
from x$kcvfh
order by 1;

-- On BCV
set linesize 120;
select hxfil FILE#,fhsta STAT,fhscn SCN, fhthr thr, fhrba_Seq SEQUENCE,fhtnm TABLESPACE
from x$kcvfh
order by 1;

Note: bug 6004226 may show different fhrba_seq column values between Primary and BCV copy.
Workaround is in step 4.

3.
Error:

RMAN> resync catalog;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 12/10/2008 07:45:32
ORA-00236: snapshot operation disallowed: mounted control file is a backup

RMAN> shutdown immediate

database dismounted
Oracle instance shut down

RMAN> run {
2> startup mount;
3> allocate channel edw_backup_sbt1 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
4> allocate channel edw_backup_sbt2 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
5> allocate channel edw_backup_sbt3 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
6> allocate channel edw_backup_sbt4 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
7> backup full filesperset = 10 as BACKUPSET tag '%d_full_backup_%TAG' database;
8> release channel edw_backup_sbt1;
9> release channel edw_backup_sbt2;
10>release channel edw_backup_sbt3;
11>release channel edw_backup_sbt4;
12>}

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 4194304000 bytes

Fixed Size 2089272 bytes
Variable Size 3976203976 bytes
Database Buffers 201326592 bytes
Redo Buffers 14684160 bytes

allocated channel: edw_backup_sbt1
channel edw_backup_sbt1: sid=4392 devtype=SBT_TAPE
channel edw_backup_sbt1: Veritas NetBackup for Oracle - Release 6.5 (2007111606)

allocated channel: edw_backup_sbt2
channel edw_backup_sbt2: sid=4386 devtype=SBT_TAPE
channel edw_backup_sbt2: Veritas NetBackup for Oracle - Release 6.5
(2007111606)

allocated channel: edw_backup_sbt3
channel edw_backup_sbt3: sid=4384 devtype=SBT_TAPE
channel edw_backup_sbt3: Veritas NetBackup for Oracle - Release 6.5 (2007111606)

allocated channel: edw_backup_sbt4
channel edw_backup_sbt4: sid=4382 devtype=SBT_TAPE
channel edw_backup_sbt4: Veritas NetBackup for Oracle - Release 6.5 (2007111606)

Starting backup at 10-DEC-08
released channel: edw_backup_sbt1
released channel: edw_backup_sbt2
released channel: edw_backup_sbt3
released channel: edw_backup_sbt4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 12/10/2008 07:47:10
RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20035: invalid high recid

4.
Following is the complete procedure to take RMAN backup of BCV copy:

STEPS :
=======

1. Primary database has to be registered in the recovery catalog.

2. Verify the current RMAN Configuration.

show all;

* autobackup controlfile OFF

3. Take the BCV cut after the production database was shutdown with "shutdown immediate;".

4. On the staging server using BCV cut, Mount Database :

$ sqlplus / as sysdba

SQL> startup mount;

5. Check the controfile_type: (Controlfile type shoud be CURRENT ):

SQL> select controlfile_type from v$database;

CONTROL
---------
CURRENT

6. Connect to RMAN in nocatalog, take a backup of the controlfile and then restore it, you will have a backup controlfile then:

a.
$ rman target /

RMAN> run {
allocate channel disk1 device type disk;
backup current controlfile FORMAT '/u00/oradata/EDW/controlfile/current_controlfile1.ctl'; }
RMAN> exit;

b.
$ rman target /

RMAN> run {
allocate channel disk1 device type disk;
restore controlfile to '/u00/oradata/EDW/controlfile/backup_controlfile1.ctl' from '/u00/oradata/EDW/controlfile/current_controlfile1.ctl'; }
RMAN> exit;

7. Edit the init.ora file to point to the new controlfile (/u00/oradata/EDW/controlfile/backup_controlfile1.ctl)

8. Shutdown database:

SQL> shutdown immediate;

9. mount database:

SQL> startup mount;

10. Check the controfile_type : (Controlfile type shoud be BACKUP) :

SQL> select controlfile_type from v$database;

CONTROL
---------
BACKUP

11. Start RMAN session connected to this database mounted and the recovery catalog :

$ rman target / catalog rman/****@prman.world

12. Execute the backup.

RMAN> run {
2> startup mount;
3> allocate channel edw_backup_sbt1 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
4> allocate channel edw_backup_sbt2 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
5> allocate channel edw_backup_sbt3 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
6> allocate channel edw_backup_sbt4 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
7> backup full filesperset = 10 as BACKUPSET tag '%d_full_backup_%TAG' database;
8> release channel edw_backup_sbt1;
9> release channel edw_backup_sbt2;
10>release channel edw_backup_sbt3;
11>release channel edw_backup_sbt4;
12>}

Imp Note: If you want to open the database, edit the init.ora file to point to the original controlfile.

How to move a datafile from one diskgroup to another diskgroup in ASM using RMAN:

2009-02-24T09:41:00.001-05:00

How to move a datafile from one diskgroup to another diskgroup in ASM using RMAN:

In this test, I will be moving the datafile from A_T1_DATA_01 to B_T1_DATA_01 diskgroup:

a. select the datafile to move:
sqlplus / as sysdba on DB:
col file_name for a50
select file_id, file_name from dba_data_files where file_id=7;

b. select the diskgroup to move:
sqlplus / as sysdba on ASM:
select GROUP_NUMBER, NAME, TOTAL_MB, FREE_MB, STATE from v$asm_diskgroup;

c. On DB:
. oraenv ORADB

rman target /

RMAN> report schema;
RMAN> sql 'alter database datafile 7 offline';
RMAN> backup as copy datafile 7 format '+B_T1_DATA_01';
RMAN> switch datafile 7 to COPY;
RMAN> recover datafile 7;
RMAN> sql 'alter database datafile 7 online';
RMAN> report schema;

d. Check and see the datafile new location:
sqlplus / as sysdba on DB:
col file_name for a50
select file_id, file_name from dba_data_files where file_id=7;

e. Remove the datafile from OLD disk group:
$ asmcmd
ASMCMD> cd A_T1_DATA_01/ORADB/datafile
ASMCMD> rm FLOW_1.776.644706747

Oracle masala at work - 2:

2009-02-15T16:49:00.010-05:00

Oracle masala at work - 2:
1. How to make Oracle data Case-Insensitive search:
2. How to setup OEM Command Line Interface:
3. How to Update RMAN Recovery Catalog with Older Archive Log's that are on the Tape:
4. How to run the Oracle RAC Add Node procedure in Silent mode:

1. How to make Oracle data Case-Insensitive search:
a. Database Level Trigger to make Oracle data Case-Insensitive:

connect sys@ORADB as sysdba

--Schema Level
CREATE OR REPLACE TRIGGER sys.make_case_insensitive_AL
AFTER LOGON ON DATABASE
DECLARE
BEGIN
IF (user = 'TEST') THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP = LINGUISTIC'; -- 10gR2
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = BINARY_CI';
END IF;
END;
/

-- DB Level
CREATE OR REPLACE TRIGGER sys.make_case_insensitive_AL
AFTER LOGON ON DATABASE
DECLARE
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP = LINGUISTIC'; -- 10gR2
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = BINARY_CI';
END;
/

b. Verifying Index usage:

connect test@ORADB
create table emp(ename varchar2(50));
insert into emp values('ViJay');
insert into emp values('VIJAY');
insert into emp values('Vijay');
commit;

create index idx1_emp on emp(ename);

exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'EMP', degree=> 2, cascade => TRUE);

SET AUTOTRACE ON;
select * from emp where ename = 'vijay'; -- No Index use
select /*+ index(emp) */ * from emp where ename = 'vijay'; -- No Index use
select * from emp where ename LIKE 'Vi%'; -- This uses IDX1 Index FULL scan

-- To make use of the Index, we need to create below Index on ename column:
create index idx2_emp on emp(NLSSORT(ename,'NLS_SORT=BINARY_CI'));

exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'EMP', degree=> 2, cascade => TRUE);

select * from emp where ename = 'vijay'; -- This uses IDX2 Index
select * from emp where ename LIKE 'Vi%'; -- This uses IDX1 Index FULL scan (NOT IDX2 Index)

2. How to setup OEM Command Line Interface:
a.
export JAVA_HOME=/u00/app/oracle/product/OEM/oms10g/jdk
export PATH=$PATH:$JAVA_HOME/bin

cd /u00/app/oracle/product/OEM/oms10g/sysman/jlib

java -jar emclikit.jar client -install_dir=/export/home/oracle/OEMCL

b.
emcli setup -url="http://atlp158:4890/em" -username=sysman -password=oracle -dir=/export/home/oracle/OEMCL -trustall -novalidate

emcli sync
emcli help
emcli help update_password
emcli get_targets

3. How to Update RMAN Recovery Catalog with Older Archive Log's that are on the Tape:
a.Identify the Backup Pieces:

rman target / catalog=rman/rman1@prman

spool log to 'x.log';
list backup of archivelog all;
spool log off;

SELECT start_time, handle
FROM v$backup_piece
WHERE TRUNC (start_time) = '01-Feb-2009'
ORDER BY start_time;

b. Attach the missing Backup Pieces to RMAN Recovery Catalog:
rman target / catalog=rman/rman1@prman

-- Syntax for one Backup Pieces
CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE 'aik6806g_1_1';

-- Syntax for more that one Backup Pieces
CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE 'aik6806g_1_1,ahk6806g_1_1,ajk6806g_1_1';

4. How to run the Oracle RAC Add Node procedure in Silent mode:
a. Run below command from the existing node:
atlp260 > /DBHome/oui/bin/addNode.sh -silent "CLUSTER_NEW_NODES={atlp259,atlp258,atlp257}" -logLevel trace -debug

Once all required DB binaries are copied from atlp260 to atlp257, atlp258 and atlp259 nodes, execute "root.sh" file:
/u00/app/oracle/product/10.2.0/DB/root.sh on atlp257, atlp258 and atlp259 nodes.

b.
Just to be sure, execute "crs_start -all" and check crs_stat -t for all the resources availability.

Database Historical Trend Analysis using OS/WEB Tiers

2008-12-15T15:55:00.001-05:00

Database Historical Trend Analysis using OS/WEB Tiers:
In this artical, I will be concentrating on capturing different levels of reports for OS/DB/WEB(IIS/Apache) tiers to perform Trend Analysis.
These reports can be useful for troubleshooting the performance or capturing the baseline of the system.
In Troubleshooting approach, Understanding the problem and Measuring the change results are the keys to resolve the issue.

OS Trend Analysis:
As part of being able to monitor the performance effectively use OS utilities. Using correct utilities to find CPU, memory and disk I/O is crucial.
Side Note: We can also use Oracle OS Watcher(OSW), it depends on how easy to refer/compare the days worth of old data.

CPU:
sar -u

CREATE TABLE SYSTEM.OSMON_SARS
(
SAMPLE_DATE DATE,
SERVER_NAME VARCHAR2(50),
USER_CPU NUMBER,
SYSTEM_CPU NUMBER,
WAITIO_CPU NUMBER,
IDLE_CPU NUMBER
);

$ cat sar.sh

#!/bin/sh
############################################################
# File Name : sar.sh
# Author : Vijay Dumpa
# Usage : sar.sh ORACLE_SID
#
# Modification History
# Who When What
# -------- -------- ----------------------------------------
#
#############################################################
##
# Set up environment for job
##
#set -x
ORACLE_SID=$1; export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab | grep -v "#" | grep $ORACLE_SID | cut -d ":" -f2`; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
SERVER_NAME=`uname -a|awk '{print $2}'`; export SERVER_NAME

sar -u > /tmp/msg$$

# Run sar and direct the output into the Oracle table . . .
cat /tmp/msg$$| tail -3 | head -1 | awk '{printf("%s %s %s %s\n",$2,$3,$4,$5)}' | while read USER_CPU SYSTEM_CPU WAITIO_CPU IDLE_CPU
do
$ORACLE_HOME/bin/sqlplus -s system/xxxxxx@$1 </dev/null
insert into system.osmon_sars(sample_date, server_name, user_cpu, system_cpu, waitio_cpu, idle_cpu)
values (SYSDATE, '$SERVER_NAME', $USER_CPU, $SYSTEM_CPU, $WAITIO_CPU, $IDLE_CPU);
commit;
exit;
EOF
done

rm /tmp/msg$$

Memory:
Swap: swap -s or vmstat

CREATE TABLE SYSTEM.OSMON_VMSTATS
(
SAMPLE_DATE DATE,
SAMPLE_TIME NUMBER,
SERVER_NAME VARCHAR2(50),
RUNQUE NUMBER,
FREE_SWAP NUMBER,
FREE_MEMORY NUMBER,
PAGE_IN NUMBER,
PAGE_OUT NUMBER
);

$ cat vmstat.sh

#!/bin/sh
############################################################
# File Name : vmstat.sh
# Author : Vijay Dumpa
# Usage : vmstat.sh ORACLE_SID
#
# Modification History
# Who When What
# -------- -------- ----------------------------------------
#
#############################################################
##
# Set up environment for job
##
#set -x
ORACLE_SID=$1; export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab | grep -v "#" | grep $ORACLE_SID | cut -d ":" -f2`; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
SERVER_NAME=`uname -a|awk '{print $2}'`; export SERVER_NAME

# sample every 10 seconds.
SAMPLE_TIME=10
vmstat ${SAMPLE_TIME} 2 > /tmp/msg$$

# Run vmstat and direct the output into the Oracle table . . .
#Solaris
cat /tmp/msg$$|sed 1,3d | awk '{printf("%s %s %s %s %s\n",$1,$8,$9,$4,$5)}' | while read RUNQUE PAGE_IN PAGE_OUT FREE_SWAP FREE_MEMORY
do
$ORACLE_HOME/bin/sqlplus -s system/xxxxxx@$1 </dev/null
insert into system.osmon_vmstats(sample_date, sample_time, server_name, runque, page_in, page_out, free_swap, free_memory)
values (SYSDATE, $SAMPLE_TIME, '$SERVER_NAME', $RUNQUE, $PAGE_IN, $PAGE_OUT, $FREE_SWAP, $FREE_MEMORY);
commit;
exit;
EOF
done

rm /tmp/msg$$

Disk I/O:
iostat -xnp.

CREATE TABLE SYSTEM.OSMON_IOSTATS
(
SAMPLE_DATE DATE,
SERVER_NAME VARCHAR2(50),
READS_PER_SEC NUMBER,
WRITES_PER_SEC NUMBER,
KB_READS_PER_SEC NUMBER,
KB_WRITES_PER_SEC NUMBER,
PERCENT_TRANS_WAIT NUMBER,
PERCENT_DISK_BUSY NUMBER,
FILE_SYSTEM_DEVICE VARCHAR2(50),
MOUNT_POINT VARCHAR2(10)
);

$ cat iostat.sh (Replace physic device names in below INSERT statement)

#!/bin/sh
############################################################
# File Name : iostat.sh
# Author : Vijay Dumpa
# Usage : iostat.sh ORACLE_SID
#
# Modification History
# Who When What
# -------- -------- ----------------------------------------
#
#############################################################
##
# Set up environment for job
##
#set -x
ORACLE_SID=$1; export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab | grep -v "#" | grep $ORACLE_SID | cut -d ":" -f2`; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
SERVER_NAME=`uname -a|awk '{print $2}'`; export SERVER_NAME

iostat -xnp > /tmp/msg_io
cat /tmp/msg_io | grep s6 > /tmp/msg$$

# Run iostat and direct the output into the Oracle table . . .
#Solaris
cat /tmp/msg$$| awk '{printf("%s %s %s %s %s %s %s\n",$1,$2,$3,$4,$9,$10,$11)}' | while read READS_PER_SEC WRITES_PER_SEC KB_READS_PER_SEC KB_WRITES_PER_SEC PERCENT_TRANS_WAIT PERCENT_DISK_BUSY FILE_SYSTEM_DEVICE
do
$ORACLE_HOME/bin/sqlplus -s system/xxxxxx@$1 </dev/null
insert into system.osmon_iostats (sample_date, server_name, reads_per_sec, writes_per_sec, kb_reads_per_sec, kb_writes_per_sec,
percent_trans_wait, percent_disk_busy, file_system_device, mount_point)
values (SYSDATE, '$SERVER_NAME', $READS_PER_SEC, $WRITES_PER_SEC, $KB_READS_PER_SEC, $KB_WRITES_PER_SEC,
$PERCENT_TRANS_WAIT, $PERCENT_DISK_BUSY, '$FILE_SYSTEM_DEVICE',
(case when '$FILE_SYSTEM_DEVICE' = 'xxxxxxxxxxxxxxxxxxxxxxxx1' then 'u01'
when '$FILE_SYSTEM_DEVICE' = 'xxxxxxxxxxxxxxxxxxxxxxxx2' then 'u02'
when '$FILE_SYSTEM_DEVICE' = 'xxxxxxxxxxxxxxxxxxxxxxxx3' then 'u03'
when '$FILE_SYSTEM_DEVICE' = 'xxxxxxxxxxxxxxxxxxxxxxxx4' then 'u04'
when '$FILE_SYSTEM_DEVICE' = 'xxxxxxxxxxxxxxxxxxxxxxxx5' then 'u05'
when '$FILE_SYSTEM_DEVICE' = 'xxxxxxxxxxxxxxxxxxxxxxxx6' then 'u06'
when '$FILE_SYSTEM_DEVICE' = 'xxxxxxxxxxxxxxxxxxxxxxxx7' then 'u07'
when '$FILE_SYSTEM_DEVICE' = 'xxxxxxxxxxxxxxxxxxxxxxxx8' then 'u08'
else 'new'
end)
);
commit;
exit;
EOF
done

rm /tmp/msg_io
rm /tmp/msg$$

DB Trend Analysis:
What STATSPACK, AWR Data is Best for Trend Analysis?
With hundreds of statistics available in STATSPACK, AWR the question arises as to what metrics are the most useful?

Sorts:
It is important to monitor the amount of sorting in the database, especially the amount of sorting that is done in the TEMP tablespace (sorts (disk)).

Physical disk reads:
The reduction of I/O is the primary goal of Oracle tuning, so a trend report showing hourly disk reads can be very useful for measuring the effect of table reorganizations the re-sequence rows into index order.

Physical disk writes:
The amount of disk writes has a great impact on the overall throughput of the Oracle database, and is especially important when rollback segment activity is critical.

I/O waits:
This is a very important metric that can be used to identify and correct I/O contention. High I/O waits occur when two or more data files are being accessed simultaneously and the read-write head movement of the DASD causes the disk to wait for the completion of I/O.

Buffer Busy Waits:
A buffer bust wait occurs when a Oracle data block resides in a buffer, but the requesting program must wait to retrieve the data block.
Buffer Busy Waits can commonly occur when a table has concurrent 'UPDATE' or 'INSERT DML' and only one freelist is defined for the table.

Redo log space requests:
Redo Log space requests occur when the log buffer is too small to hold the volume of redo log activity.

Latch pin hit ratio:
The pin hit ratios for the database instance give a good idea of the sizing of the shared_pool_size parameter in the init.ora file.

Table Fetch Continued Row:
This metric give a general indication of database migrated/chained rows. When chaining becomes excessive, additional I/O is required to service a query, and the DBA should increase PCTFREE for the table and reorganize the table to remove the chains.

Web Page Load Trend Analysis:
It's very importance to see, how the web pages are getting benefited by the particular DB change or any change(like Code, OS etc..).

Idea is to use IIS, Apache log file to generate webpage load report for that day.
Some thing like this:

Total Pages served : 22606

Pages 0 to 5 secs : 20041 (88.65%)
Pages 6 to 10 secs : 1515 (6.70%)
Pages 11 to 30 secs : xxxx (3%)
Pages more than 31 secs : xxxx (2.65%)

and Display TOP 10 pages URL's.

How to generate a web page load report from IIS log:

Step 1:
As part of this, you have to enable extended logging for cs-uri-stem and time-taken fields and also make sure the positions of the fields are 6 and 15 in IIS log file.

Step 2:
Perl Script: gen_IIS_webpage_load_report.pl (Schedule below script to run daily from Windows scheduler at 11:50pm after modifying email names, subject and log file location)

#!perl

use Time::localtime;
use Net::SMTP;
use Mail::Sender;

my $month = localtime->mon + 1;
#print "Month: $month\n";
my $day = localtime->mday;
#print "Day: $day\n";
my $year = localtime->year + 1900;
$year = sprintf("%02d", $year % 100);
my $hour = localtime->hour;
my $min = localtime->min;
my $second = localtime->sec;

if ($month < 10) {
$month = "0$month";
}
if ($day < 10) {
$day = "0$day";
# print "Day after 0: $day\n";
}
if ($hour < 10) {
$hour = "0$hour";
}
if ($min < 10) {
$min = "0$min";
}
if ($second < 10) {
$second = "0$second";
}

my $tmpDate = `date \/T`;
my $tmpTime = $hour . ":" . $min . ":" . $second;

my $dateNow = $tmpDate . $tmpTime;

my $time = $year . $month . $day;
#print "Time: $time\n";

my $alog = "c:\\perl_scripts\\windows\\" . "ex" . $time . ".log";
#print "Alog: $alog\n";

my $tmpFile0 = "c:\\perl_scripts\\windows\\tmplist0." . $time;
#print "TmpFile0: $tmpFile0\n";
my $tmpFile1 = "c:\\perl_scripts\\windows\\tmplist1." . $time;
#print "TmpFile1: $tmpFile1\n";
my $tmpFile2 = "c:\\perl_scripts\\windows\\tmplist2." . $time;
#print "TmpFile2: $tmpFile2\n";
my $tmpFile3 = "c:\\perl_scripts\\windows\\tmplist3." . $time;
#print "TmpFile3: $tmpFile3\n";
my $tmpFile4 = "c:\\perl_scripts\\windows\\tmplist4." . $time . ".html";
#print "TmpFile4: $tmpFile4\n";
my $tmpFile5 = "c:\\perl_scripts\\windows\\tmplist5." . $time;
#print "TmpFile5: $tmpFile5\n";
my $tmpFile6 = "c:\\perl_scripts\\windows\\tmplist6." . $time;
#print "TmpFile6: $tmpFile6\n";
my $tmpFile7 = "c:\\perl_scripts\\windows\\tmplist7." . $time;
#print "TmpFile6: $tmpFile7\n";

my $plusZero = 0;
my $plusFive = 0;
my $plusTen = 0;
my $plusFifteen = 0;
my $plusTwenty = 0;
my $plusTwentyFive = 0;
my $plusThirty = 0;
my $pageCnt = 0;
my $lineCnt = 0;

open (TMPFILE5, ">>$tmpFile5") or die "Cannot open $tmpFile5: $!";
open (TMPFILE0, ">$tmpFile0") or die "Cannot open $tmpFile0: $!";
open (LOG, "$alog") or die "Cannot open $alog: $!";
while () {
my $line = $_;
# print "Line from log: $line\n";
chomp $line;
$lineCnt++;
($date,$fileTime,$sitname,$computerName,$sip,$method,$uriStem,$query,$port,$username,$cip,$csVersion,$csUserAgent,$csCookie,$csReferer,$csHost,$scStatus,$subStatus,$win32Status,$scBytes,$csBytes,$timeTaken) = split(/\s+/, $line);
if ($lineCnt == 4) {
if ($line !~ /time-taken$/) {
sendMailMessage("message.xyzCompanyname.com", "firstname.lastname\@xyzCompanyname.com", "firstname.lastname\@xyzCompanyname.com", "$alog is not formatted as expected. Time taken field in $alog is blank.\n", "XYZsite Prod Report - Log not formatted correctly");
#system ("c:\\blat.exe \"$alog is not formatted as expected. Time taken field in $alog is blank.\n\" -server message.xyzCompanyname.com -f firstname.lastname\@xyzCompanyname.com -to firstname.lastname\@xyzCompanyname.com -s \"Log not formatted correctly\"");
exit 1;
}
}
#print "TimeTaken: $timeTaken\n";
if ($uriStem =~ m/.asp/) {
# print "$uriStem : $timeTaken\n";
print TMPFILE0 $uriStem . " " . $timeTaken . "\n";
}
$pageCnt++;
#print "PageCnt: $pageCnt\n";

if ($timeTaken < 500) {
$plusZero++;
# print "$plusZero\n";
} elsif ($timeTaken < 1000) {
$plusFive++;
# print "$plusFive\n";
} elsif ($timeTaken < 1500) {
$plusTen++;
# print "$plusTen\n";
} elsif ($timeTaken < 2000) {
$plusFifteen++;
# print "$plusFifteen\n";
} elsif ($timeTaken < 2500) {
$plusTwenty++;
# print "$plusTwenty\n";
} elsif ($timeTaken < 3000) {
$plusTwentyFive++;
# print "$plusTwentyFive\n";
} elsif ($timeTaken >= 3000) {
$plusThirty++;
#print "$plusThirty\n";
}
}

print TMPFILE5 "PAGECNT:$pageCnt\n";
print TMPFILE5 "PLUSZERO:$plusZero\n";
print TMPFILE5 "PLUSFIVE:$plusFive\n";
print TMPFILE5 "PLUSTEN:$plusTen\n";
print TMPFILE5 "PLUSFIFTEEN:$plusFifteen\n";
print TMPFILE5 "PLUSTWENTY:$plusTwenty\n";
print TMPFILE5 "PLUSTWENTYFIVE:$plusTwentyFive\n";
print TMPFILE5 "PLUSTHIRTY:$plusThirty\n";

close(LOG);
close(TMPFILE0);
close(TMPFILE5);

open (TMPFILE0, "$tmpFile0") or die "Cannot open $tmpFile0: $!";
#open (TMPFILE1, ">$tmpFile1") or die "Cannot open $tmpFile1: $!";
open (TMPFILE2, ">$tmpFile2") or die "Cannot open $tmpFile2: $!";

my @unique = ();
my @long = ();

while () {
my $line2 = $_;
chomp($line2);
($uriStem, $timeTaken) = split(/\s+/, $line2);

unless($i{$_}++) {
push(@long, $timeTaken);
}
}

foreach $time3 (@long) {
printf TMPFILE2 ("%09d", $time3);
print TMPFILE2 "\n";
}
close(TMPFILE2);

open (TMPFILE2, "$tmpFile2") or die "Cannot open $tmpFile2: $!";
my @long2 = ;
close(TMPFILE2);

@sorted = reverse sort @long2;
#print "sorted: @sorted\n";

my $count = 0;

open (TMPFILE6, ">$tmpFile6") or die "Cannot open $tmpFile6: $!";
foreach $sortedTime (@sorted) {
$count++;
if ($count <= 10) {
$sortedTime =~ s/^0*//;
print TMPFILE6 $sortedTime;
}
}
close(TMPFILE6);
close(TMPFILE0);
#close(TMPFILE1);
close(TMPFILE2);

open (TMPFILE6, "$tmpFile6") or die "Cannot open $tmpFile6: $!";
my @sortedTimes = ;
#print "SortedTimes: @sortedTimes\n";
close(TMPFILE6);

my @maxTime = ();
open (TMPFILE3, ">$tmpFile3") or die "Cannot open $tmpFile3: $!\n";
open (TMPFILE0, "$tmpFile0") or die "Cannot open $tmpFile0: $!";
my $match = "";

while () {
my $line2 = $_;
chomp($line2);
#print "Line2: $line\n";
foreach $num (@sortedTimes) {
if (/$num/) {
$match = $line2;
print TMPFILE3 "$match\n";
}
}
#($timeTaken) = split(/\s+/, $line2);
#push(@unique, $uriStem);

}

close(TMPFILE0);
#close(TMPFILE1);
close(TMPFILE2);
close(TMPFILE3);

my @temp = ();
my @sortTemp = ();
open (TMPFILE7, ">$tmpFile7") or die "Cannot open $tmpFile7: $!";
open (TMPFILE3, "$tmpFile3") or die "Cannot open $tmpFile3: $!";
while () {
my $line4 = $_;
chomp($line);
($uri, $ttaken) = split(/\s+/, $line4);
# print "$uri : $ttaken";
$ttaken2 = sprintf("%09d", $ttaken);
# print "T Taken: $ttaken2\n";
print TMPFILE7 "$ttaken2:$uri\n";
}
close(TMPFILE7);
close(TMPFILE3);

open (TMPFILE7, "$tmpFile7") or die "Cannot open $tmpFile7: $!";
@temp = ;
@sortTemp = reverse sort @temp;
#print "Sort Temp: @sortTemp\n";
close(TMPFILE7);

open (TMPFILE5, "$tmpFile5") or die "Cannot open $tmpFile5: $!";
while () {
my $line3 = $_;
chomp($line3);
($label, $field) = split (/:/, $line3);
if ($label eq "PAGECNT") {
$pageCnt = $field;
} elsif ($label eq "PLUSZERO") {
$plusZero = $field;
} elsif ($label eq "PLUSFIVE") {
$plusFive = $field;
} elsif ($label eq "PLUSTEN") {
$plusTen = $field;
} elsif ($label eq "PLUSFIFTEEN") {
$plusFifteen = $field;
} elsif ($label eq "PLUSTWENTY") {
$plusTwenty = $field;
} elsif ($label eq "PLUSTWENTYFIVE") {
$plusTwentyFive = $field;
} elsif ($label eq "PLUSTHIRTY") {
$plusThirty = $field;
}
}

close(TMPFILE5);

my $plusZeroCalc = ($plusZero/$pageCnt) * 100;
my $plusFiveCalc = ($plusFive/$pageCnt) * 100;
my $plusTenCalc = ($plusTen/$pageCnt) * 100;
my $plusFifteenCalc = ($plusFifteen/$pageCnt) * 100;
my $plusTwenyCalc = ($plusTwenty/$pageCnt) * 100;
my $plusTwentyFiveCalc = ($plusTwentyFive/$pageCnt) * 100;
my $plusThirtyCalc = ($plusThirty/$pageCnt) * 100;

open (TMPFILE4, ">>$tmpFile4") or die "Cannot open $tmpFile4: $!";
print TMPFILE4 "Content-Type: text/html; charset=\"US-ASCII\"";

print TMPFILE4 "< HTML>

Total Pages served :$pageCnt
";
print TMPFILE4 "
Pages 0 to 5 secs :$plusZero (";
printf TMPFILE4 ("%.2f",$plusZeroCalc);
print TMPFILE4 "\%)\n";
print TMPFILE4 "
Pages 6 to 10 secs :$plusFive (";
printf TMPFILE4 ("%.2f",$plusFiveCalc);
print TMPFILE4 "\%)\n";
print TMPFILE4 "
Pages 11 to 15 secs :$plusTen (";
printf TMPFILE4 ("%.2f",$plusTenCalc);
print TMPFILE4 "\%)\n";
print TMPFILE4 "
Pages 16 to 20 secs :$plusFifteen (";
printf TMPFILE4 ("%.2f",$plusFifteenCalc);
print TMPFILE4 "\%)\n";
print TMPFILE4 "
Pages 21 to 25 secs :$plusTwenty (";
printf TMPFILE4 ("%.2f",$plusTwentyCalc);
print TMPFILE4 "\%\n";
print TMPFILE4 "
Pages 26 to 30 secs :$plusTwentyFive (";
printf TMPFILE4 ("%.2f",$plusTwentyFiveCalc);
print TMPFILE4 "\%)\n";
print TMPFILE4 "
Pages +31 secs :$plusThirty (";
printf TMPFILE4 ("%.2f",$plusThirtyCalc);
print TMPFILE4 "\%)\n";

print TMPFILE4 "

Top 10 Slow XYZsite Production Web Pages for $dateNow

";
foreach my $line5 (@sortTemp) {
($ttake, $url) = split(/:/, $line5);
my $tt = $ttake;
$tt =~ s/^0*//;
print TMPFILE4 "URL: $url\n";
print TMPFILE4 "
MilliSeconds: $tt\n

";
}
close(TMPFILE4);

$sender = new Mail::Sender {[from => 'firstname.lastname@xyzCompanyname.com'], [to => 'firstname.lastname@xyzCompanyname.com']};
(ref ($sender->MailFile( {from => 'firstname.lastname@xyzCompanyname.com', to => 'firstname.lastname@xyzCompanyname.com, firstname2.lastname2@xyzCompanyname.com', smtp=> 'message.xyzCompanyname.com', subject=>"XYZsite Prod Web Page Report - $tmpDate", msg => "XYZsite Prod Web Page Report - $tmpDate\n\n", file => "$tmpFile4"})) and print "Mail sent OK.") or die "$Mail::Sender::Error\n";

unlink "$tmpFile0";
unlink "$tmpFile1";
unlink "$tmpFile2";
unlink "$tmpFile3";
unlink "$tmpFile4";
unlink "$tmpFile5";
unlink "$tmpFile6";
unlink "$tmpFile7";

sub sendMailMessage {
my $serverName = shift @_;
my $fromAddress = shift @_;
my $toAddress = shift @_;
my $theMessage = shift @_;
my $theSubject = shift @_;
$smtp = Net::SMTP->new($serverName);
$smtp->mail($fromAddress);
$smtp->to($toAddress);
$smtp->data();
$smtp->datasend("To: $toAddress");
$smtp->datasend("\n");
$smtp->datasend("Subject: $theSubject \n");
$smtp->datasend("\n");
$smtp->datasend($theMessage);
$smtp->dataend();
$smtp->quit;
}

How to generate a web page load report from Apache log:

Step 1:
Add below like in the apache config's file.

LogFormat "%h %l %u %t \"%r\" %>s %b \"%{User-Agent}i\" %T"
Note the %T at the end. This is where those #'s come from. The "normal" apache config doesn't usually include that %T.

Step 2:
Shell Script: gen_apache_webpage_load_report.sh (Schedule below script to runs daily from cron at 11:50pm after modifying email names, subject and log file location)

$ cat gen_apache_webpage_load_report.sh

#!/bin/ksh
############################################################
# File Name : gen_apache_webpage_load_report.sh
# Author : Vijay Dumpa
# Usage : gen_apache_webpage_load_report.sh
#
# Modification History
# Who When What
# -------- -------- ----------------------------------------
#
#############################################################
##
# Set up environment for the job
##
#set -x
TMPFILE0=/tmp/tmplist0.$$
TMPFILE1=/tmp/tmplist1.$$
TMPFILE2=/tmp/tmplist2.$$
TMPFILE3=/tmp/tmplist3.$$
TMPFILE4=/tmp/tmplist4.$$

ALOG=/local/apache/logs/https/access_log.`date +%y%m%d`
MAILEES=firstname.lastname@xyzCompanyname.com

PLUSZERO=0
PLUSFIVE=0
PLUSTEN=0
PLUSFIFTEEN=0
PLUSTWENTY=0
PLUSTWENTYFIVE=0
PLUSTHIRTY=0
PAGECNT=0

# filter the original Apche access log to get the two required fields, and sort them by URL and then in numeric order per URL
egrep -v '.gif|.css|.js|.jpg|.htm|.bmp' $ALOG | awk '{print $NF, " ", $7}' | awk -F\? '{print $1}' | sort -k 2,2 -k 1,1rn > $TMPFILE0

exec 3<&0 < $TMPFILE0
while read -r INPUT
do
PAGECNT=`expr $PAGECNT + 1`
NUM=`echo $INPUT | awk '{print $1}'`

if [ $NUM -lt 6 ]; then
PLUSZERO=`expr $PLUSZERO + 1`
elif [ $NUM -lt 11 ]; then
PLUSFIVE=`expr $PLUSFIVE + 1`
elif [ $NUM -lt 16 ]; then
PLUSTEN=`expr $PLUSTEN + 1`
elif [ $NUM -lt 21 ]; then
PLUSFIFTEEN=`expr $PLUSFIFTEEN + 1`
elif [ $NUM -lt 26 ]; then
PLUSTWENTY=`expr $PLUSTWENTY + 1`
elif [ $NUM -lt 31 ]; then
PLUSTWENTYFIVE=`expr $PLUSTWENTYFIVE + 1`
elif [ $NUM -ge 31 ]; then
PLUSTHIRTY=`expr $PLUSTHIRTY + 1`
fi
done
exec 0<&3 3<&-

# filter out the highest value for each URL and make a new list of the top 25
uniq -f 1 $TMPFILE0 | sort -rn | head -25 > $TMPFILE1

# sort the main list of all URLs and all times in numeric order, highest first
sort -rn $TMPFILE0 > $TMPFILE2

# Get all of the timings for each page view, and then take the top 50 columns
exec 3<&0 < $TMPFILE1
while read -r INPUT
do
TMP1=`echo $INPUT | awk '{print $2}'`
TMP2=`grep " $TMP1" $TMPFILE2 | awk '{printf "%s, ",$1}'`
TMP3=`echo $TMP2 | cut -c 1-120 | awk -F, '{print $1" "$2" "$3" "$4" "$5" "$6" "$7" "$8" "$9" "$10" "$11" "$12" "$13" "$14" "$15" "$16" "$17" "$18" "$19" "$20" "$21" "$22" "$23" "$24" "$25}'`
echo $TMP3:$TMP1 >> $TMPFILE3
done
exec 0<&3 3<&-

echo "Subject:"XYZsite" Prod web page report - `date`" > $TMPFILE4
echo "Content-Type: text/html; charset=\"US-ASCII\"" >> $TMPFILE4

echo "< HTML>Total Pages served :$PAGECNT\n
" >> $TMPFILE4
echo "
Pages 0 to 5 secs :$PLUSZERO (`echo $PLUSZERO $PAGECNT | awk '{printf "%.2f",(($1/$2)*100)}'`%)" >> $TMPFILE4
echo "
Pages 6 to 10 secs :$PLUSFIVE (`echo $PLUSFIVE $PAGECNT | awk '{printf "%.2f",(($1/$2)*100)}'`%)" >> $TMPFILE4
echo "
Pages 11 to 15 secs :$PLUSTEN (`echo $PLUSTEN $PAGECNT | awk '{printf "%.2f",(($1/$2)*100)}'`%)" >> $TMPFILE4
echo "
Pages 16 to 20 secs :$PLUSFIFTEEN (`echo $PLUSFIFTEEN $PAGECNT | awk '{printf "%.2f",(($1/$2)*100)}'`%)" >> $TMPFILE4
echo "
Pages 21 to 25 secs :$PLUSTWENTY (`echo $PLUSTWENTY $PAGECNT | awk '{printf "%.2f",(($1/$2)*100)}'`%)" >> $TMPFILE4
echo "
Pages 26 to 30 secs :$PLUSTWENTYFIVE (`echo $PLUSTWENTYFIVE $PAGECNT | awk '{printf "%.2f",(($1/$2)*100)}'`%)" >> $TMPFILE4
echo "
Pages +31 secs :$PLUSTHIRTY (`echo $PLUSTHIRTY $PAGECNT | awk '{printf "%.2f",(($1/$2)*100)}'`%)\n" >> $TMPFILE4

echo "

Top 10 Page times of the top 25 Slow "XYZsite" Production Web Pages for `date`

" >> $TMPFILE4

# re-sort the list with the slowest pages first
sort -rn $TMPFILE3 | awk -F: '{print "URL: "$2"\n
Seconds: \n"$1"\n

"}' >> $TMPFILE4

cat $TMPFILE4 | /usr/lib/sendmail $MAILEES

rm $TMPFILE0 $TMPFILE1 $TMPFILE2 $TMPFILE3 $TMPFILE4

How to Shrink the datafiles in Oracle

2008-10-17T12:14:00.000-04:00

1. Script to generate and run to set the maxbytes of the datafiles and shrink the datafiles:
SET echo off head off serveroutput off termout off feedback off verify off space 0;
SET linesize 150;
SET pagesize 0;

COLUMN value new_val blksize;
SELECT VALUE
FROM v$parameter
WHERE NAME = 'db_block_size'
/

SPOOL gen_setmaxbytes.sql
SELECT 'spool gen_setmaxbytes.log'
FROM DUAL;
SELECT 'alter database datafile '''
|| file_name
|| ''' autoextend on next 100m maxsize '
|| BYTES / 1024 / 1024
|| 'M;'
FROM dba_data_files
WHERE maxbytes < BYTES AND maxbytes > 0
UNION ALL
SELECT 'alter database datafile '''
|| file_name
|| ''' autoextend on next 100m maxsize '
|| BYTES / 1024 / 1024
|| 'M;'
FROM dba_data_files
WHERE maxbytes = 0
UNION ALL
SELECT 'alter database datafile '''
|| file_name
|| ''' resize '
|| maxbytes / 1024 / 1024
|| 'M;'
FROM dba_data_files
WHERE maxbytes < BYTES AND maxbytes > 0
/
SELECT 'spool off;'
FROM DUAL;
SPOOL off;

SPOOL gen_shrink_dbfiles.sql
SELECT 'spool gen_shrink_dbfiles.log'
FROM DUAL;
SELECT 'alter database datafile '''
|| file_name
|| ''' resize '
|| CEIL ((NVL (hwm, 1) * &&blksize) / 1024 / 1024)
|| 'm;' cmd
FROM dba_data_files a,
(SELECT file_id, MAX (block_id + blocks - 1) hwm
FROM dba_extents
GROUP BY file_id) b
WHERE a.file_id = b.file_id(+)
AND CEIL (blocks * &&blksize / 1024 / 1024)
- CEIL ((NVL (hwm, 1) * &&blksize) / 1024 / 1024) > 0
/
SELECT 'spool off;'
FROM DUAL;
SPOOL off;

SET echo on feedback on;

@gen_setmaxbytes.sql
@gen_shrink_dbfiles.sql

EXIT;

2. Script to generate rebuild objects at the end of the given datafile:
Note: Replace the file name with actual datafile name; which you are not able to shrink after reorganization, because some objects have extents at the end of the datafile. Check and change the below script for partition tables.

SELECT mst.altst
FROM (SELECT 'ALTER TABLE ' || owner || '.' || segment_name
|| ' MOVE;' altst,
block_id bid
FROM dba_extents
WHERE file_id = (SELECT file_id
FROM dba_data_files
WHERE file_name = '/u01/oradata/ORASID/ts01.dbf')
AND segment_type = 'TABLE'
UNION
SELECT 'ALTER INDEX '
|| owner
|| '.'
|| segment_name
|| ' REBUILD;' altst,
block_id bid
FROM dba_extents
WHERE file_id = (SELECT file_id
FROM dba_data_files
WHERE file_name = '/u01/oradata/ORASID/ts01.dbf')
AND segment_type = 'INDEX'
ORDER BY bid DESC) mst;

Database and Server Inventory Management using Oracle Application Express, or APEX (Oracle HTML DB):

2008-10-03T18:09:00.000-04:00

1. Download Oracle Application Express:
http://www.oracle.com/technology/software/products/database/xe/htdocs/102xelinsoft.html

2. Install rpm:
Login as root!!!

rpm -ivh oracle-xe-univ-10.2.0.1-1.0.i386.rpm

Output:
[root@dbasvr1 oraclexp]# rpm -ivh oracle-xe-univ-10.2.0.1-1.0.i386.rpm
Preparing... ########################################### [100%]
1:oracle-xe-univ ########################################### [100%]
Executing Post-install steps...

You must run '/etc/init.d/oracle-xe configure' as the root user to
configure the database.
[root@dbasvr1 oraclexp]#

3. Run /etc/init.d/oracle-xe configure as the root user:

Output:
[root@dbasvr1 oraclexp]# /etc/init.d/oracle-xe configure

Oracle Database 10g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 10g Express
Edition. The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts. Press to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:

Specify a port that will be used for the database listener [1521]:

Port 1521 appears to be in use by another application. Please specify a different port.
Specify a port that will be used for the database listener [1521]:1525

Specify a password to be used for database accounts. Note that the same
password will be used for SYS and SYSTEM. Oracle recommends the use of
different passwords for each database account. This can be done after
initial configuration: -- oracle
Confirm the password: -- oracle

Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:y

Starting Oracle Net Listener...Done
Configuring Database...Done
Starting Oracle Database 10g Express Edition Instance...Done
Installation Completed Successfully.
To access the Database Home Page go to "http://dbasvr1:8080/apex"
[root@dbasvr1 oraclexp]#

To start the database manually, run this command:
$ /etc/init.d/oracle-xe start

To stop the database manually, use the following command:
$ /etc/init.d/oracle-xe stop

4. Login and Create users:
Login as system user and goto Administration > Manage Database Users and create below users.

hp_dba/hp_dba
vdumpa/vdumpa
guest/guest

5. Create the database inventory table in hp_dba schema to store the information:

create sequence hp_dba.db_inv_seq start with 1 increment by 1;

create table hp_dba.db_inv(
db_no number(10) primary key,
Business_Unit varchar2(40),
db_name varchar2(20),
instance_name varchar2(20),
server_name varchar2(30),
Server_config varchar2(50),
DB_version varchar2(20),
OS_version varchar2(20),
DB_Env varchar2(30),
DB_size varchar2(20),
SGA_size varchar2(20),
PGA_size varchar2(20),
backup_type varchar2(40),
backup_frequencey varchar2(40),
physical_location varchar2(20),
verified_date date,
verified_by varchar2(40),
Primary_DBA varchar2(30),
Secondary_DBA varchar2(30),
Customer_name VARCHAR2(40),
DB_Control_URL varchar2(100),
Description varchar2(100))
tablespace users;

grant select on hp_dba.db_inv to guest;
grant select, insert, update, delete on hp_dba.db_inv to vdumpa;

CREATE OR REPLACE TRIGGER hp_dba.db_inv_BI
BEFORE INSERT
ON hp_dba.db_inv REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
if :new.DB_NO is null then
select db_inv_seq.nextval
into :new.DB_NO
from dual;
end if;
end;

6. Login as hp_dba user to create Application:

a. On the Database Home Page, click the Application Builder icon.
b. Click the Create button.
c. Under Create Application, select Create Application and click Next.
d. Under Create Application:
Name: Enter "dbinv".
Accept the remaining defaults(Application(100)).
Click Next.
Next, add pages to your application.
e. Under Add Page:
For Select Page Type, select Report and Form
Next to the Table Name field, click the up arrow, and then select db_inv
from the Search Dialog window.
Click Add Page.
Click Next.
f. On the Tabs panel, select Two Level of Tabs and click Next.
g. On the Shared Components panel, accept the default (No) and click Next.
h. For Authentication Scheme, Language, and User Language Preference Derived From,
accept the defaults and click Next.
i. For the theme, select Theme 2 click Next.
j. After you click Create, the following message displays at the top of the page:
Application created successfully.

7. Running Your New Application:

a. To run your application, Click the Run Application icon.
b. In the log in page, enter hp_dba for both the User Name and Password
c. Search/Edit/Create the values from this page.

8. Public guest user access link:
http://dbasvr1:8080/apex/f?p=100:1

9. Backing Up the schema data:
EX: /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs > cat /etc/oratab
XE:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server:N

EX: /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs > . oraenv
ORACLE_SID = [EX] ? XE

XE: /home/oracle > exp hp_dba/hp_dba file=exp_hp_dba.dmp

Export: Release 10.2.0.1.0 - Production on Fri Oct 17 09:00:24 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user HP_DBA
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user HP_DBA
About to export HP_DBA's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export HP_DBA's tables via Conventional Path ...
. . exporting table DB_INV 37 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

RAC unique backup and restore challenges using OEM Grid Control in 10g:

2008-07-02T17:49:00.000-04:00

How different is to backup and restore the RAC database VS. single instance database?

Consider the architectural nature of the Oracle RAC cluster:
In RAC setup, you have at least two different servers, oracle uses this hardware by creating two instances,
one on each node, with their own instance memory area(SGA).
Each instance has its own redo logs, but they exist on the shared disk and are accessible by all the nodes in the cluster.
All controlfiles and datafiles are shared between the two/more instances in the cluster.

RMAN perspective, this architecture creates interesting challenges for taking backups and restores, rman can connect to only a single node.
This shouldn't pose any problems for taking backup, but we do have a interesting challenge when it comes to restore.
In other words, we have to start the restore from the same node as backup was performed or need to use parms "ENV=(NB_ORA_CLIENT=atlt47)" in your allocate channel command.

CASE:
Scenario:
In one of our application, customer pre-requisite is to set NLS_DATE_FORMAT to 'RRRR-MON-DD' (default is 'DD-MON-RR').

Steps:
a.
Setting the NLS_DATE_FORMAT to 'RRRR-MON-DD' and restarting the RAC database:
SQL> alter system set NLS_DATE_FORMAT='RRRR-MON-DD' scope=spfile sid='*';
$ srvctl stop database -d DBName
$ srvctl start database -d DBName

b.
Resetting the NLS_DATE_FORMAT to default: <<<<< user set NLS_DATE_FORMAT to NULL, thinking resetting it to default value!!! and got oracle internal error.
SQL> alter system set NLS_DATE_FORMAT=NULL scope=spfile sid='*';

$ srvctl stop database -d DBName
$ srvctl start database -d DBName

ORA-00600: internal error code, arguments: [kccsbck_first], [1], [2792458534], [], [], [], [], []

c.
1.
So user decided to restore the DB to just before step a backup (Yes, user did take full rman backup!!!)

Important points to observe about the rman full backup:
Backup was taken from OEM GRID on cluster database level, instead of instance level.
In this case backup was taken from node 2(instance 2).
RMAN backup configured to VTL's (Via VERITAS NetBackup for Oracle - Release 5.1).

2.
User who scheduled a rman restore from OEM GRID, thinking that OEM GRID can automatically detect the backup performed node and restores it to point of time requested in the restore.

3.
What happened?
This time restore job connected to node 1(instance 1) and started the restore and recovery.

Did the restore successful?
Yes, but it restored the latest backup taken from the node 1(instance 1), which is 19 days old and it reset the logs to open the database.

RMAN error log and restoring 19 days old backup from OEM GRID:

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> SQL> Connected to an idle instance.
SQL> SQL> ORACLE instance started.

Total System Global Area 1157627904 bytes
Fixed Size 2072224 bytes
Variable Size 285213024 bytes
Database Buffers 855638016 bytes
Redo Buffers 14704640 bytes
Database mounted.
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

Recovery Manager: Release 10.2.0.3.0 - Production on Sun Jun 29 16:26:28 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN>
connected to target database: ORADB (DBID=390676631, not open)

RMAN>
connected to recovery catalog database

RMAN>
echo set on

RMAN> run {
2> restore database;
3> recover database noredo;
4> }
Starting restore at 29-JUN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1077 instance=ORADB1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=1076 instance=ORADB1 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=1075 instance=ORADB1 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=1074 instance=ORADB1 devtype=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=1073 instance=ORADB1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: sid=1072 instance=ORADB1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_2: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: sid=1071 instance=ORADB1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_3: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: sid=1070 instance=ORADB1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_4: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)
allocated channel: ORA_SBT_TAPE_5
channel ORA_SBT_TAPE_5: sid=1069 instance=ORADB1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_5: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)
allocated channel: ORA_SBT_TAPE_6
channel ORA_SBT_TAPE_6: sid=1068 instance=ORADB1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_6: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)
allocated channel: ORA_SBT_TAPE_7
channel ORA_SBT_TAPE_7: sid=1067 instance=ORADB1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_7: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)
allocated channel: ORA_SBT_TAPE_8
channel ORA_SBT_TAPE_8: sid=1066 instance=ORADB1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_8: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)

channel ORA_SBT_TAPE_2: starting datafile backupset restore
channel ORA_SBT_TAPE_2: specifying datafile(s) to restore from backup set
restoring datafile 00005 to +INF_T1_BACKUP_01/qroa1inf/datafile/users.265.657026093
channel ORA_SBT_TAPE_2: reading from backup piece 0qjk51um_1_1
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to +INF_T1_BACKUP_01/qroa1inf/datafile/infinys_data1.ora
channel ORA_SBT_TAPE_1: reading from backup piece 0sjk51un_1_1
channel ORA_SBT_TAPE_3: starting datafile backupset restore
channel ORA_SBT_TAPE_3: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs1.261.657026085
channel ORA_SBT_TAPE_3: reading from backup piece 0pjk51um_1_1
channel ORA_SBT_TAPE_4: starting datafile backupset restore
channel ORA_SBT_TAPE_4: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs2.264.657026091
channel ORA_SBT_TAPE_4: reading from backup piece 0rjk51un_1_1
channel ORA_SBT_TAPE_5: starting datafile backupset restore
channel ORA_SBT_TAPE_5: specifying datafile(s) to restore from backup set
restoring datafile 00003 to +INF_T1_BACKUP_01/qroa1inf/datafile/sysaux.262.657026087
channel ORA_SBT_TAPE_5: reading from backup piece 0ojk51um_1_1
channel ORA_SBT_TAPE_6: starting datafile backupset restore
channel ORA_SBT_TAPE_6: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +INF_T1_BACKUP_01/qroa1inf/datafile/system.260.657026077
channel ORA_SBT_TAPE_6: reading from backup piece 0njk51um_1_1
ORA-19870: error reading backup piece 0sjk51un_1_1
ORA-19507: failed to retrieve sequential file, handle="0sjk51un_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0sjk51un_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0qjk51um_1_1
ORA-19507: failed to retrieve sequential file, handle="0qjk51um_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0qjk51um_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0pjk51um_1_1
ORA-19507: failed to retrieve sequential file, handle="0pjk51um_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0pjk51um_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0rjk51un_1_1
ORA-19507: failed to retrieve sequential file, handle="0rjk51un_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0rjk51un_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0ojk51um_1_1
ORA-19507: failed to retrieve sequential file, handle="0ojk51um_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0ojk51um_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0njk51um_1_1
ORA-19507: failed to retrieve sequential file, handle="0njk51um_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0njk51um_1_1> not found in NetBackup catalog
failover to previous backup

channel ORA_SBT_TAPE_3: starting datafile backupset restore
channel ORA_SBT_TAPE_3: specifying datafile(s) to restore from backup set
restoring datafile 00003 to +INF_T1_BACKUP_01/qroa1inf/datafile/sysaux.262.657026087
channel ORA_SBT_TAPE_3: reading from backup piece 0gjk2tut_1_1
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs2.264.657026091
channel ORA_SBT_TAPE_1: reading from backup piece 0jjk2tuu_1_1
channel ORA_SBT_TAPE_2: starting datafile backupset restore
channel ORA_SBT_TAPE_2: specifying datafile(s) to restore from backup set
restoring datafile 00005 to +INF_T1_BACKUP_01/qroa1inf/datafile/users.265.657026093
channel ORA_SBT_TAPE_2: reading from backup piece 0ijk2tuu_1_1
channel ORA_SBT_TAPE_4: starting datafile backupset restore
channel ORA_SBT_TAPE_4: specifying datafile(s) to restore from backup set
restoring datafile 00006 to +INF_T1_BACKUP_01/qroa1inf/datafile/infinys_data1.ora
channel ORA_SBT_TAPE_4: reading from backup piece 0kjk2tuu_1_1
channel ORA_SBT_TAPE_5: starting datafile backupset restore
channel ORA_SBT_TAPE_5: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs1.261.657026085
channel ORA_SBT_TAPE_5: reading from backup piece 0hjk2tuu_1_1
channel ORA_SBT_TAPE_6: starting datafile backupset restore
channel ORA_SBT_TAPE_6: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +INF_T1_BACKUP_01/qroa1inf/datafile/system.260.657026077
channel ORA_SBT_TAPE_6: reading from backup piece 0fjk2tut_1_1
ORA-19870: error reading backup piece 0jjk2tuu_1_1
ORA-19507: failed to retrieve sequential file, handle="0jjk2tuu_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0jjk2tuu_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0ijk2tuu_1_1
ORA-19507: failed to retrieve sequential file, handle="0ijk2tuu_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0ijk2tuu_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0gjk2tut_1_1
ORA-19507: failed to retrieve sequential file, handle="0gjk2tut_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0gjk2tut_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0kjk2tuu_1_1
ORA-19507: failed to retrieve sequential file, handle="0kjk2tuu_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0kjk2tuu_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0hjk2tuu_1_1
ORA-19507: failed to retrieve sequential file, handle="0hjk2tuu_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0hjk2tuu_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0fjk2tut_1_1
ORA-19507: failed to retrieve sequential file, handle="0fjk2tut_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0fjk2tut_1_1> not found in NetBackup catalog
failover to previous backup

the filename for datafile 6 is missing in the control file
channel ORA_SBT_TAPE_4: starting datafile backupset restore
channel ORA_SBT_TAPE_4: specifying datafile(s) to restore from backup set
restoring datafile 00003 to +INF_T1_BACKUP_01/qroa1inf/datafile/sysaux.262.657026087
channel ORA_SBT_TAPE_4: reading from backup piece 09jjvff1_1_1
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to +INF_T1_BACKUP_01/qroa1inf/datafile/users.265.657026093
channel ORA_SBT_TAPE_1: reading from backup piece 0djjvff2_1_1
channel ORA_SBT_TAPE_2: starting datafile backupset restore
channel ORA_SBT_TAPE_2: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs2.264.657026091
channel ORA_SBT_TAPE_2: reading from backup piece 0bjjvff1_1_1
channel ORA_SBT_TAPE_3: starting datafile backupset restore
channel ORA_SBT_TAPE_3: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs1.261.657026085
channel ORA_SBT_TAPE_3: reading from backup piece 0ajjvff1_1_1
channel ORA_SBT_TAPE_5: starting datafile backupset restore
channel ORA_SBT_TAPE_5: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +INF_T1_BACKUP_01/qroa1inf/datafile/system.260.657026077
channel ORA_SBT_TAPE_5: reading from backup piece 08jjvff1_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=0djjvff2_1_1 tag=BACKUP_ORADB.WO_062708093125
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:03:25
channel ORA_SBT_TAPE_2: restored backup piece 1
piece handle=0bjjvff1_1_1 tag=BACKUP_ORADB.WO_062708093125
channel ORA_SBT_TAPE_2: restore complete, elapsed time: 00:03:40
channel ORA_SBT_TAPE_3: restored backup piece 1
piece handle=0ajjvff1_1_1 tag=BACKUP_ORADB.WO_062708093125
channel ORA_SBT_TAPE_3: restore complete, elapsed time: 00:03:40
channel ORA_SBT_TAPE_4: restored backup piece 1
piece handle=09jjvff1_1_1 tag=BACKUP_ORADB.WO_062708093125
channel ORA_SBT_TAPE_4: restore complete, elapsed time: 00:03:40
channel ORA_SBT_TAPE_5: restored backup piece 1
piece handle=08jjvff1_1_1 tag=BACKUP_ORADB.WO_062708093125
channel ORA_SBT_TAPE_5: restore complete, elapsed time: 00:03:40
Finished restore at 29-JUN-08

Starting recover at 29-JUN-08
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_SBT_TAPE_4
using channel ORA_SBT_TAPE_5
using channel ORA_SBT_TAPE_6
using channel ORA_SBT_TAPE_7
using channel ORA_SBT_TAPE_8
temporary file +INF_T1_BACKUP_01/qroa1inf/tempfile/temp.263.657026087 size altered in control file
Finished recover at 29-JUN-08

RMAN> exit;

Recovery Manager complete.

Recovery Manager: Release 10.2.0.3.0 - Production on Sun Jun 29 16:31:36 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN>
connected to target database: ORADB (DBID=390676631, not open)

RMAN>
connected to recovery catalog database

RMAN>
echo set on

RMAN> alter database open resetlogs;
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> exit;

Recovery Manager complete.

Action plan:
Connect to rman catalog (very important, default is NOCATALOG) and
reset the database incarnation Key to PARENT(before resetlogs) from the node 2(rman backup performed node).
Restore controlfile and database and recover the database to open.

RMAN restore log:
atlt47 | ORADB2 | /u00/app/oracle/product/10.2.0/DB
> rman TARGET / CATALOG rman/rman@prman.world

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 30 12:20:24 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORADB (not mounted)
connected to recovery catalog database

RMAN> shutdown immediate;

set DBID 390676631;

startup nomount;

Oracle instance shut down

RMAN>
RMAN>
executing command: SET DBID
database name is "ORADB" and DBID is 390676631

RMAN>
RMAN>
connected to target database (not started)
Oracle instance started

Total System Global Area 1157627904 bytes

Fixed Size 2072224 bytes
Variable Size 285213024 bytes
Database Buffers 855638016 bytes
Redo Buffers 14704640 bytes

RMAN> LIST INCARNATION OF DATABASE ORADB;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
4710871 4710872 ORADB 390676631 PARENT 1 10-JUN-08
4710871 4931854 ORADB 390676631 CURRENT 1971243 29-JUN-08

RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
4710871 4710872 ORADB 390676631 PARENT 1 10-JUN-08
4710871 4931854 ORADB 390676631 CURRENT 1971243 29-JUN-08

-------------------------------------------- <<<<<<<< giving the wrong(CURRENT) database incarnation Key.
RMAN> reset database to incarnation 4931854;

database reset to incarnation 4931854

RMAN> run {
2> set until time "to_date('JUN 29 2008 12:21:00','Mon DD YYYY HH24:MI:SS')";
3> allocate channel d1 type 'SBT_TAPE';
4> allocate channel d2 type 'SBT_TAPE';
5> allocate channel d3 type 'SBT_TAPE';
6> allocate channel d4 type 'SBT_TAPE';
7> restore controlfile;
8> sql "alter database mount";
9> restore database;
10> recover database;
11> sql "alter database open resetlogs";
12> release channel d1;
13> release channel d2;
14> release channel d3;
15> release channel d4;
16> }

executing command: SET until clause
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 06/30/2008 13:29:08
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
--------------------------------------------

RMAN> reset database to incarnation 4710872; <<<<<<<< giving the correct(PARENT) database incarnation Key.
database reset to incarnation 4710872

RMAN> run {
2> set until time "to_date('JUN 29 2008 12:21:00','Mon DD YYYY HH24:MI:SS')";
3> allocate channel d1 type 'SBT_TAPE';
4> allocate channel d2 type 'SBT_TAPE';
5> allocate channel d3 type 'SBT_TAPE';
6> allocate channel d4 type 'SBT_TAPE';
7> restore controlfile;
8> sql "alter database mount";
9> restore database;
10> recover database;
11> sql "alter database open resetlogs";
12> release channel d1;
13> release channel d2;
14> release channel d3;
15> release channel d4;
16> }

executing command: SET until clause

released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
released channel: ORA_SBT_TAPE_1
released channel: ORA_SBT_TAPE_2
released channel: ORA_SBT_TAPE_3
released channel: ORA_SBT_TAPE_4
released channel: ORA_SBT_TAPE_5
released channel: ORA_SBT_TAPE_6
released channel: ORA_SBT_TAPE_7
released channel: ORA_SBT_TAPE_8
allocated channel: d1
channel d1: sid=1079 instance=ORADB2 devtype=SBT_TAPE
channel d1: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)

allocated channel: d2
channel d2: sid=1080 instance=ORADB2 devtype=SBT_TAPE
channel d2: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)

allocated channel: d3
channel d3: sid=1078 instance=ORADB2 devtype=SBT_TAPE
channel d3: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)

allocated channel: d4
channel d4: sid=1077 instance=ORADB2 devtype=SBT_TAPE
channel d4: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)

Starting restore at 30-JUN-08

channel d1: starting datafile backupset restore
channel d1: restoring control file
channel d1: reading from backup piece 0tjk51un_1_1

channel d1: restored backup piece 1
piece handle=0tjk51un_1_1 tag=BACKUP_ORADB.WO_062908121740
channel d1: restore complete, elapsed time: 00:03:35
output filename=+INF_T1_DATA_01/qroa1inf/controlfile/current.290.658762389
Finished restore at 30-JUN-08

sql statement: alter database mount

Starting restore at 30-JUN-08

channel d2: starting datafile backupset restore
channel d2: specifying datafile(s) to restore from backup set
restoring datafile 00005 to +INF_T1_BACKUP_01/qroa1inf/datafile/users.265.657026093
channel d2: reading from backup piece 0qjk51um_1_1
channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to +INF_T1_BACKUP_01/qroa1inf/datafile/infinys_data1.ora
channel d1: reading from backup piece 0sjk51un_1_1
channel d1: restored backup piece 1
piece handle=0sjk51un_1_1 tag=BACKUP_ORADB.WO_062908121740
channel d1: restore complete, elapsed time: 00:03:25
channel d2: restored backup piece 1
piece handle=0qjk51um_1_1 tag=BACKUP_ORADB.WO_062908121740
channel d2: restore complete, elapsed time: 00:03:25
channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs2.264.657026091
channel d1: reading from backup piece 0rjk51un_1_1
channel d2: starting datafile backupset restore
channel d2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs1.261.657026085
channel d2: reading from backup piece 0pjk51um_1_1
channel d1: restored backup piece 1
piece handle=0rjk51un_1_1 tag=BACKUP_ORADB.WO_062908121740
channel d1: restore complete, elapsed time: 00:03:25
channel d2: restored backup piece 1
piece handle=0pjk51um_1_1 tag=BACKUP_ORADB.WO_062908121740
channel d2: restore complete, elapsed time: 00:03:25
channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to +INF_T1_BACKUP_01/qroa1inf/datafile/sysaux.262.657026087
channel d1: reading from backup piece 0ojk51um_1_1
channel d1: restored backup piece 1
piece handle=0ojk51um_1_1 tag=BACKUP_ORADB.WO_062908121740
channel d1: restore complete, elapsed time: 00:03:35
channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +INF_T1_BACKUP_01/qroa1inf/datafile/system.260.657026077
channel d1: reading from backup piece 0njk51um_1_1
channel d1: restored backup piece 1
piece handle=0njk51um_1_1 tag=BACKUP_ORADB.WO_062908121740
channel d1: restore complete, elapsed time: 00:03:35
Finished restore at 30-JUN-08

Starting recover at 30-JUN-08

starting media recovery

released channel: d1
released channel: d2
released channel: d3
released channel: d4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/30/2008 13:47:58
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 2 seq 13 lowscn 2607439 found to restore
RMAN-06025: no backup of log thread 1 seq 26 lowscn 2723046 found to restore

RMAN>
RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/30/2008 13:49:38
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open RESETLOGS;

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> exit

Recovery Manager complete.

Side Note:
To avoid manual "alter database open RESETLOGS;" use below rman commands.

recover database noredo; -- For NOARCHIVE LOG
recover database; -- For ARCHIVE LOG

P.S.
So, how is my list backupset command looks with NOCATALOG and CATALOG?

atlt47 | ORADB2 | /u00/app/oracle/product/10.2.0/DB> rman target / <<<<<<<<<< NOCATALOG
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 30 11:01:30 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORADB (DBID=390676631, not open)

RMAN> list backupset;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 256.00K SBT_TAPE 00:01:28 13-JUN-08
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 06jircsb_1_1 Media:
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/users.265.657026093

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 256.00K SBT_TAPE 00:01:36 13-JUN-08
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 07jircsb_1_1 Media:
SPFILE Included: Modification time: 13-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 14.75M SBT_TAPE 00:01:37 13-JUN-08
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 05jircsb_1_1 Media:
Control File Included: Ckp SCN: 862074 Ckp time: 13-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 5.00M SBT_TAPE 00:01:43 13-JUN-08
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 04jircsa_1_1 Media:
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs2.264.657026091

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 210.25M SBT_TAPE 00:01:55 13-JUN-08
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 03jircsa_1_1 Media:
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/sysaux.262.657026087

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 333.25M SBT_TAPE 00:01:56 13-JUN-08
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 02jircsa_1_1 Media:
List of Datafiles in backup set 6
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs1.261.657026085

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 356.50M SBT_TAPE 00:01:58 13-JUN-08
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 01jircsa_1_1 Media:
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/system.260.657026077

RMAN>

RMAN> exit

Recovery Manager complete.

atlt47 | ORADB2 | /u00/app/oracle/product/10.2.0/DB> rman target / <<<<<<<<<< CATALOG
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 30 11:07:03 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORADB (DBID=390676631, not open)

RMAN> connect catalog rman/rman@prman.world <<<<<<<<<< CATALOG

connected to recovery catalog database

RMAN> list backup;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4728914 Full 256.00K SBT_TAPE 00:01:28 13-JUN-08
BP Key: 4728921 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 06jircsb_1_1 Media:
List of Datafiles in backup set 4728914
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/users.265.657026093

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4728915 Full 256.00K SBT_TAPE 00:01:36 13-JUN-08
BP Key: 4728922 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 07jircsb_1_1 Media:
SPFILE Included: Modification time: 13-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4728916 Full 14.75M SBT_TAPE 00:01:37 13-JUN-08
BP Key: 4728923 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 05jircsb_1_1 Media:
Control File Included: Ckp SCN: 862074 Ckp time: 13-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4728917 Full 5.00M SBT_TAPE 00:01:43 13-JUN-08
BP Key: 4728924 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 04jircsa_1_1 Media:
List of Datafiles in backup set 4728917
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs2.264.657026091

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4728918 Full 210.25M SBT_TAPE 00:01:55 13-JUN-08
BP Key: 4728925 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 03jircsa_1_1 Media:
List of Datafiles in backup set 4728918
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/sysaux.262.657026087

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4728919 Full 333.25M SBT_TAPE 00:01:56 13-JUN-08
BP Key: 4728926 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 02jircsa_1_1 Media:
List of Datafiles in backup set 4728919
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs1.261.657026085

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4728920 Full 356.50M SBT_TAPE 00:01:58 13-JUN-08
BP Key: 4728927 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 01jircsa_1_1 Media:
List of Datafiles in backup set 4728920
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/system.260.657026077

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4904245 Full 256.00K SBT_TAPE 00:01:17 27-JUN-08
BP Key: 4904253 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062708093125
Handle: 0djjvff2_1_1 Media:
List of Datafiles in backup set 4904245
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 1971242 27-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/users.265.657026093

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4904246 Full 14.75M SBT_TAPE 00:01:21 27-JUN-08
BP Key: 4904254 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062708093125
Handle: 0cjjvff1_1_1 Media:
Control File Included: Ckp SCN: 1971242 Ckp time: 27-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4904247 Full 256.00K SBT_TAPE 00:01:26 27-JUN-08
BP Key: 4904255 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062708093125
Handle: 0ejjvff2_1_1 Media:
SPFILE Included: Modification time: 27-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4904248 Full 21.25M SBT_TAPE 00:01:31 27-JUN-08
BP Key: 4904256 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062708093125
Handle: 0bjjvff1_1_1 Media:
List of Datafiles in backup set 4904248
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 1971242 27-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs2.264.657026091

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4904249 Full 333.25M SBT_TAPE 00:01:45 27-JUN-08
BP Key: 4904257 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062708093125
Handle: 0ajjvff1_1_1 Media:
List of Datafiles in backup set 4904249
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 1971242 27-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs1.261.657026085

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4904250 Full 344.00M SBT_TAPE 00:01:47 27-JUN-08
BP Key: 4904258 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062708093125
Handle: 09jjvff1_1_1 Media:
List of Datafiles in backup set 4904250
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 1971242 27-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/sysaux.262.657026087

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4904251 Full 362.00M SBT_TAPE 00:01:47 27-JUN-08
BP Key: 4904259 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062708093125
Handle: 08jjvff1_1_1 Media:
List of Datafiles in backup set 4904251
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1971242 27-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/system.260.657026077

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4920736 Full 14.75M SBT_TAPE 00:01:32 28-JUN-08
BP Key: 4920745 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062808045704
Handle: 0ljk2tuu_1_1 Media:
Control File Included: Ckp SCN: 2589364 Ckp time: 28-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4920737 Full 256.00K SBT_TAPE 00:01:42 28-JUN-08
BP Key: 4920746 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062808045704
Handle: 0mjk2tuu_1_1 Media:
SPFILE Included: Modification time: 28-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4920738 Full 155.25M SBT_TAPE 00:01:46 28-JUN-08
BP Key: 4920747 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062808045704
Handle: 0jjk2tuu_1_1 Media:
List of Datafiles in backup set 4920738
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 2589364 28-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs2.264.657026091

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4920739 Full 124.50M SBT_TAPE 00:01:52 28-JUN-08
BP Key: 4920748 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062808045704
Handle: 0ijk2tuu_1_1 Media:
List of Datafiles in backup set 4920739
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 2589364 28-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/users.265.657026093

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4920740 Full 347.50M SBT_TAPE 00:01:55 28-JUN-08
BP Key: 4920749 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062808045704
Handle: 0gjk2tut_1_1 Media:
List of Datafiles in backup set 4920740
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 2589364 28-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/sysaux.262.657026087

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4920741 Full 256.00K SBT_TAPE 00:01:56 28-JUN-08
BP Key: 4920750 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062808045704
Handle: 0kjk2tuu_1_1 Media:
List of Datafiles in backup set 4920741
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 2589364 28-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/infinys_data1.ora

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4920742 Full 333.25M SBT_TAPE 00:01:58 28-JUN-08
BP Key: 4920751 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062808045704
Handle: 0hjk2tuu_1_1 Media:
List of Datafiles in backup set 4920742
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 2589364 28-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs1.261.657026085

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4920743 Full 471.00M SBT_TAPE 00:02:02 28-JUN-08
BP Key: 4920752 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062808045704
Handle: 0fjk2tut_1_1 Media:
List of Datafiles in backup set 4920743
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2589364 28-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/system.260.657026077

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4929973 Full 14.75M SBT_TAPE 00:01:23 29-JUN-08
BP Key: 4929982 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062908121740
Handle: 0tjk51un_1_1 Media:
Control File Included: Ckp SCN: 2780259 Ckp time: 29-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4929974 Full 256.00K SBT_TAPE 00:01:31 29-JUN-08
BP Key: 4929983 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062908121740
Handle: 0ujk51un_1_1 Media:
SPFILE Included: Modification time: 29-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4929975 Full 256.00K SBT_TAPE 00:01:32 29-JUN-08
BP Key: 4929984 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062908121740
Handle: 0sjk51un_1_1 Media:
List of Datafiles in backup set 4929975
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 2780259 29-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/infinys_data1.ora

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4929976 Full 137.00M SBT_TAPE 00:01:40 29-JUN-08
BP Key: 4929985 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062908121740
Handle: 0qjk51um_1_1 Media:
List of Datafiles in backup set 4929976
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 2780259 29-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/users.265.657026093

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4929977 Full 333.25M SBT_TAPE 00:01:41 29-JUN-08
BP Key: 4929986 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062908121740
Handle: 0pjk51um_1_1 Media:
List of Datafiles in backup set 4929977
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 2780259 29-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs1.261.657026085

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4929978 Full 155.25M SBT_TAPE 00:01:42 29-JUN-08
BP Key: 4929987 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062908121740
Handle: 0rjk51un_1_1 Media:
List of Datafiles in backup set 4929978
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 2780259 29-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs2.264.657026091

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4929979 Full 348.25M SBT_TAPE 00:01:49 29-JUN-08
BP Key: 4929988 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062908121740
Handle: 0ojk51um_1_1 Media:
List of Datafiles in backup set 4929979
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 2780259 29-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/sysaux.262.657026087

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4929980 Full 471.25M SBT_TAPE 00:01:52 29-JUN-08
BP Key: 4929989 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062908121740
Handle: 0njk51um_1_1 Media:
List of Datafiles in backup set 4929980
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2780259 29-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/system.260.657026077

RMAN>

RMAN> exit

Recovery Manager complete.

ASM could not mount diskgroup or see the disks:

2008-05-31T11:48:00.001-04:00

What might have caused it? What are my options to fix or recover?

What might have caused it?:
a.
asm_diskstring and/or asm_diskgroups init parameters are not set correctly

b.
OS Disks/Devices ownership

c.
OS Disks/Devices permissions

d.
Check if Physical Disks/Devices exist or visible at OS level

e.
Make sure oracle ASMLib rpm's are installed. - Linux

f.
Make sure all the disks can be listed

g.
Make sure all the disks can be queried

h.
Make sure permission and ownership of /var/opt/oracle directory.

i.
Make sure that there are no duplicate paths or names that point to the same physical disk

Specific to RAC:
j.
Make sure disk name and underlying logical device id or LUN id is consistent across all the nodes

k.
Make sure /proc/partitions file is consistent (no missing entries) across the nodes

What are my options to fix or recover?: Let's talk about all the options mentioned above.
a.
asm_diskstring and/or asm_diskgroups init parameters are not set correctly

Raw device:
ALTER SYSTEM SET asm_diskstring = '/dev/raw/raw*, /dev/raw/raw1[1-5]' scope=BOTH sid='*';

ASMLib:
ALTER SYSTEM SET asm_diskstring = 'ORCL:*' scope=BOTH sid='*';

ALTER SYSTEM SET asm_diskgroups = 'ORADB_DATA, ORADB_IDX, ORADB_BACKUP, ORADB_ARC' scope=spfile sid='*';

b.
OS Disks/Devices ownership

oracle:dba for non OCR and Voting Disk.

root:dba for OCR.
oracle:dba for Voting Disk.

c.
OS Disks/Devices permissions

660 - for non OCR and Voting Disk.

640 - for OCR.
644 - for Voting Disk.

d.
Check if Physical Disks/Devices exist or visible at OS level

Raw device:
/dev/raw

ASMLib:
/dev/oracleasm/disks

Side Note: For more information, please refer to my other note on "ASM SAN migration Case Study: - How to find mapping of ASM disks to Physical Devices?"

e.
Make sure oracle ASMLib rpm's are installed. - Linux

ASMLib only!!

Check:
rpm -qa|grep asm
oracleasmlib-2.0.2-1
oracleasm-support-2.0.3-1
oracleasm-2.6.9-42.ELsmp-2.0.3-1

f.
Make sure all the disks can be listed

/etc/init.d/oracleasm listdisks

g.
Make sure all the disks can be queried

List all the disks:
for i in `cd /dev/oracleasm/disks;ls *`;
do
/etc/init.d/oracleasm querydisk $i 2>/dev/null
done

Look for any missing label's:
for i in `ls /dev/emcpower*1`;
do
/etc/init.d/oracleasm querydisk $i 2>/dev/null | grep "\"\"$"
done

$/dev/oracleasm/disks> ls -lt AO_6011_0972
brw-rw---- 1 oracle dba 120, 641 Mar 21 17:03 AO_6011_0972

$/dev/oracleasm/disks> /etc/init.d/oracleasm querydisk AO_6011_0972
Disk "AO_6011_0972" is a valid ASM disk on device [120, 641]

Side Note 1: When you create ASM disks, its best practice to follow some kind of naming convention.
Why?
For example, easy to recreate missing labels or easy to identify physical storage.

CLARiiON:
$ ls -lt /dev/emcpowerem
brwxrwx--- 1 oracle dba 120, 2272 Mar 18 10:04 /dev/emcpowerem
$ ls -lt /dev/emcpowerem1
brwxrwx--- 1 oracle dba 120, 2273 Apr 24 17:32 /dev/emcpowerem1
$ ls -lt /dev/oracleasm/disks/EM*
brw-rw---- 1 oracle dba 120, 2273 Mar 18 10:46 /dev/oracleasm/disks/EM_5438_1227

# /sbin/powermt display dev=emcpowerem
Pseudo name=emcpowerem
CLARiiON ID=APM00043005438 [d03_ORADB_Dev]
Logical device ID=60060160272013007325DC0E5FF4DC11 [LUN 1227]
state=alive; policy=CLAROpt; priority=0; queued-IOs=0
Owner: default=SP B, current=SP B
==============================================================================
---------------- Host --------------- - Stor - -- I/O Path - -- Stats ---
### HW Path I/O Paths Interf. Mode State Q-IOs Errors
==============================================================================
1 lpfc sdabf SP B1 active alive 0 0
1 lpfc sdacp SP A1 active alive 0 0
1 lpfc sdadz SP B0 active alive 0 0
1 lpfc sdafj SP A0 active alive 0 0
2 lpfc sdagt SP B2 active alive 0 0
2 lpfc sdaid SP A2 active alive 0 0
2 lpfc sdajn SP B3 active alive 0 0
2 lpfc sdakx SP A3 active alive 0 0

Naming Standards:
For CLARiiON: [emcpower Id]_[CLARiiON Id(last 4 dig)]_[LUN Id]
For EMC DMX : [emcpower Id]_[Symmetrix Id(last 4 dig)]_[Logical device Id]

EMC DMX:
$ /dev> ls -lt emcpoweray
brwxrwx--- 1 oracle dba 120, 800 Apr 22 05:46 emcpoweray
$ /dev> /etc/init.d/oracleasm listdisks | grep AY
AY_0469_0BB9
$ /dev> /sbin/powermt display dev=emcpoweray
Pseudo name=emcpoweray
Symmetrix ID=000187880469
Logical device ID=0BB9
state=alive; policy=SymmOpt; priority=0; queued-IOs=0
==============================================================================
---------------- Host --------------- - Stor - -- I/O Path - -- Stats ---
### HW Path I/O Paths Interf. Mode State Q-IOs Errors
==============================================================================
1 lpfc sdbg FA 3cA active alive 0 0
2 lpfc sddv FA 14cA active alive 0 0

Side Note 2: Always take backup of all ASMLib disk headers.

for i in `cd /dev/oracleasm/disks/;ls *`;
do
dd if=/dev/oracleasm/disks/$i of=/tmp/$i.dump bs=4096 count=1
done

h.
Make sure permission and ownership of /var/opt/oracle directory.

oracle:dba and relax the permissions

i.
Make sure that there are no duplicate paths or names that point to the same physical disk
Such duplication result in the below error in alert log file.

ORA-15020: discovered duplicate ASM disk ""

We can detect this using this command: "/sbin/powermt display dev=emcpowerem"

j.
Make sure disk name and underlying logical device id or LUN id is consistent across the nodes

Some times, disk name pointing to logical device id or LUN id on one node is different from other nodes.

We can detect this using this command: "/sbin/powermt display dev=emcpowerem"
We can correct this using this command: "emcpadm rename -s emcpoweram -t emcpowerah"

k.
Make sure /proc/partitions file is consistent(no missing entries) across the nodes

Case 1:
ASMLib could not see SAN storage after reboot or missing ASMLib labels?:

How to create the kfed utility?:
from DB home:
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk ikfed

We can recreate missing labels with below command:
# /etc/init.d/oracleasm force-renamedisk /dev/emcpoweray1 AY_0469_0BB9

Side Note: We can also look at ASM labels using kfed tool, kfed tool might have been used to tweak the headers.

kfed help in 10.2.0.3:
$ kfed help=y
as/mlib ASM Library [asmlib='lib']
aun/um AU number to examine or update [AUNUM=number]
aus/z Allocation Unit size in bytes [AUSZ=number]
blkn/um Block number to examine or update [BLKNUM=number]
blks/z Metadata block size in bytes [BLKSZ=number]
ch/ksum Update checksum before each write [CHKSUM=YES/NO]
cn/t Count of AUs to process [CNT=number]
d/ev ASM device to examine or update [DEV=string]
o/p KFED operation type [OP=READ/WRITE/MERGE/NEW/FORM/FIND/STRUCT]
p/rovnm Name for provisioning purposes [PROVNM=string]
te/xt File name for translated block text [TEXT=string]
ty/pe ASM metadata block type number [TYPE=number]
KFED-01000: USAGE: kfed [] [] []

e.g.:
$ ORACLE_ASM_HOME/bin/kfed read /dev/oracleasm/disks/AY_0469_0BB9
kfbh.endian: 1 ; 0x000: 0x01
kfbh.hard: 130 ; 0x001: 0x82
kfbh.type: 1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt: 1 ; 0x003: 0x01
kfbh.block.blk: 0 ; 0x004: T=0 NUMB=0x0
kfbh.block.obj: 2147483650 ; 0x008: TYPE=0x8 NUMB=0x2
kfbh.check: 2426726764 ; 0x00c: 0x90a4e96c
kfbh.fcn.base: 0 ; 0x010: 0x00000000
kfbh.fcn.wrap: 0 ; 0x014: 0x00000000
kfbh.spare1: 0 ; 0x018: 0x00000000
kfbh.spare2: 0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:ORCLDISKAY_0469_0BB9 ; 0x000: length=20
kfdhdb.driver.reserved[0]: 811555137 ; 0x008: 0x305f5941
kfdhdb.driver.reserved[1]: 1597584948 ; 0x00c: 0x5f393634
kfdhdb.driver.reserved[2]: 960643632 ; 0x010: 0x39424230
kfdhdb.driver.reserved[3]: 0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]: 0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]: 0 ; 0x01c: 0x00000000
kfdhdb.compat: 168820736 ; 0x020: 0x0a100000
kfdhdb.dsknum: 2 ; 0x024: 0x0002
kfdhdb.grptyp: 1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts: 3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname: AY_0469_0BB9 ; 0x028: length=12
kfdhdb.grpname: ORADB_T1_BACKUP_01 ; 0x048: length=18
kfdhdb.fgname: AY_0469_0BB9 ; 0x068: length=12
kfdhdb.capname: ; 0x088: length=0
kfdhdb.crestmp.hi: 32902473 ; 0x0a8: HOUR=0x9 DAYS=0xa MNTH=0x3 YEAR=0x7d8
kfdhdb.crestmp.lo: 1396390912 ; 0x0ac: USEC=0x0 MSEC=0x2cf SECS=0x33 MINS=0x14
kfdhdb.mntstmp.hi: 32904078 ; 0x0b0: HOUR=0xe DAYS=0x1c MNTH=0x4 YEAR=0x7d8
kfdhdb.mntstmp.lo: 1783004160 ; 0x0b4: USEC=0x0 MSEC=0x19f SECS=0x24 MINS=0x1a
kfdhdb.secsize: 512 ; 0x0b8: 0x0200
kfdhdb.blksize: 4096 ; 0x0ba: 0x1000
kfdhdb.ausize: 1048576 ; 0x0bc: 0x00100000
kfdhdb.mfact: 113792 ; 0x0c0: 0x0001bc80
kfdhdb.dsksize: 17263 ; 0x0c4: 0x0000436f
kfdhdb.pmcnt: 2 ; 0x0c8: 0x00000002
kfdhdb.fstlocn: 1 ; 0x0cc: 0x00000001
kfdhdb.altlocn: 2 ; 0x0d0: 0x00000002
kfdhdb.f1b1locn: 0 ; 0x0d4: 0x00000000
kfdhdb.redomirrors[0]: 0 ; 0x0d8: 0x0000
kfdhdb.redomirrors[1]: 0 ; 0x0da: 0x0000
kfdhdb.redomirrors[2]: 0 ; 0x0dc: 0x0000
kfdhdb.redomirrors[3]: 0 ; 0x0de: 0x0000
kfdhdb.dbcompat: 168820736 ; 0x0e0: 0x0a100000
kfdhdb.grpstmp.hi: 32902473 ; 0x0e4: HOUR=0x9 DAYS=0xa MNTH=0x3 YEAR=0x7d8
kfdhdb.grpstmp.lo: 1396293632 ; 0x0e8: USEC=0x0 MSEC=0x270 SECS=0x33 MINS=0x14
kfdhdb.ub4spare[0]: 0 ; 0x0ec: 0x00000000
kfdhdb.ub4spare[1]: 0 ; 0x0f0: 0x00000000
kfdhdb.ub4spare[2]: 0 ; 0x0f4: 0x00000000
kfdhdb.ub4spare[3]: 0 ; 0x0f8: 0x00000000
kfdhdb.ub4spare[4]: 0 ; 0x0fc: 0x00000000
kfdhdb.ub4spare[5]: 0 ; 0x100: 0x00000000
kfdhdb.ub4spare[6]: 0 ; 0x104: 0x00000000
kfdhdb.ub4spare[7]: 0 ; 0x108: 0x00000000
kfdhdb.ub4spare[8]: 0 ; 0x10c: 0x00000000
kfdhdb.ub4spare[9]: 0 ; 0x110: 0x00000000
kfdhdb.ub4spare[10]: 0 ; 0x114: 0x00000000
kfdhdb.ub4spare[11]: 0 ; 0x118: 0x00000000
kfdhdb.ub4spare[12]: 0 ; 0x11c: 0x00000000
kfdhdb.ub4spare[13]: 0 ; 0x120: 0x00000000
kfdhdb.ub4spare[14]: 0 ; 0x124: 0x00000000
kfdhdb.ub4spare[15]: 0 ; 0x128: 0x00000000
kfdhdb.ub4spare[16]: 0 ; 0x12c: 0x00000000
kfdhdb.ub4spare[17]: 0 ; 0x130: 0x00000000
kfdhdb.ub4spare[18]: 0 ; 0x134: 0x00000000
kfdhdb.ub4spare[19]: 0 ; 0x138: 0x00000000
kfdhdb.ub4spare[20]: 0 ; 0x13c: 0x00000000
kfdhdb.ub4spare[21]: 0 ; 0x140: 0x00000000
kfdhdb.ub4spare[22]: 0 ; 0x144: 0x00000000
kfdhdb.ub4spare[23]: 0 ; 0x148: 0x00000000
kfdhdb.ub4spare[24]: 0 ; 0x14c: 0x00000000
kfdhdb.ub4spare[25]: 0 ; 0x150: 0x00000000
kfdhdb.ub4spare[26]: 0 ; 0x154: 0x00000000
kfdhdb.ub4spare[27]: 0 ; 0x158: 0x00000000
kfdhdb.ub4spare[28]: 0 ; 0x15c: 0x00000000
kfdhdb.ub4spare[29]: 0 ; 0x160: 0x00000000
kfdhdb.ub4spare[30]: 0 ; 0x164: 0x00000000
kfdhdb.ub4spare[31]: 0 ; 0x168: 0x00000000
kfdhdb.ub4spare[32]: 0 ; 0x16c: 0x00000000
kfdhdb.ub4spare[33]: 0 ; 0x170: 0x00000000
kfdhdb.ub4spare[34]: 0 ; 0x174: 0x00000000
kfdhdb.ub4spare[35]: 0 ; 0x178: 0x00000000
kfdhdb.ub4spare[36]: 0 ; 0x17c: 0x00000000
kfdhdb.ub4spare[37]: 0 ; 0x180: 0x00000000
kfdhdb.ub4spare[38]: 0 ; 0x184: 0x00000000
kfdhdb.ub4spare[39]: 0 ; 0x188: 0x00000000
kfdhdb.ub4spare[40]: 0 ; 0x18c: 0x00000000
kfdhdb.ub4spare[41]: 0 ; 0x190: 0x00000000
kfdhdb.ub4spare[42]: 0 ; 0x194: 0x00000000
kfdhdb.ub4spare[43]: 0 ; 0x198: 0x00000000
kfdhdb.ub4spare[44]: 0 ; 0x19c: 0x00000000
kfdhdb.ub4spare[45]: 0 ; 0x1a0: 0x00000000
kfdhdb.ub4spare[46]: 0 ; 0x1a4: 0x00000000
kfdhdb.ub4spare[47]: 0 ; 0x1a8: 0x00000000
kfdhdb.ub4spare[48]: 0 ; 0x1ac: 0x00000000
kfdhdb.ub4spare[49]: 0 ; 0x1b0: 0x00000000
kfdhdb.ub4spare[50]: 0 ; 0x1b4: 0x00000000
kfdhdb.ub4spare[51]: 0 ; 0x1b8: 0x00000000
kfdhdb.ub4spare[52]: 0 ; 0x1bc: 0x00000000
kfdhdb.ub4spare[53]: 0 ; 0x1c0: 0x00000000
kfdhdb.ub4spare[54]: 0 ; 0x1c4: 0x00000000
kfdhdb.ub4spare[55]: 0 ; 0x1c8: 0x00000000
kfdhdb.ub4spare[56]: 0 ; 0x1cc: 0x00000000
kfdhdb.ub4spare[57]: 0 ; 0x1d0: 0x00000000
kfdhdb.acdb.aba.seq: 0 ; 0x1d4: 0x00000000
kfdhdb.acdb.aba.blk: 0 ; 0x1d8: 0x00000000
kfdhdb.acdb.ents: 0 ; 0x1dc: 0x0000
kfdhdb.acdb.ub2spare: 0 ; 0x1de: 0x0000

Case 2:
Logical device id or LUN id is different across the nodes?:
Please refer to above session j for fix

Case 3:
Missing disk(can't mount diskgroup/drop diskgroup/delete disk)?:

Problem:
Why am I getting following errors, when I am doing "alter diskgroup ORADB_DATA_DG mount;" command?

ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "7" is missing

Consider all the above mentioned missing disk possibilities to fix the problem. If it's permanent disk failures, please refer below "Recovering the disks from permanent disk failures? session"

I
Recovering the disks from permanent disk failures?

10g:
Method 1.
Use DD command to clear disk header and delete the disk and create the disk, which will make the disks as candidates and add back to ASM diskgroup.

dd if=/dev/zero of=[raw device] bs=1024 count=4 -- for raw device
or
dd if=/dev/zero of=[emcpower device] bs=1024 count=4 -- for emcpower device

as root only!!!
/etc/init.d/oracleasm deletedisk [ASM disk name]
/etc/init.d/oracleasm createdisk [ASM disk name] [emcpower device]

/etc/init.d/oracleasm scandisks

Method 2.
create diskgroup EXTERNAL REDUNDANCY
disk '<1'st missing disk name>' force, '<2'nd missing disk name>' force;

Now the question is how do I drop the original diskgroup?
When you dd or force all the disks to use new diskgroup, old ASM diskgroup will not exist any longer. byeee, byee bye

11g:
drop diskgroup force including contents; -- Drop the diskgroup completely, to recreate the diskgroup.

alter diskgroup mount force; -- This is useful when you have missing disk or disks

Side Note: You can not use the FORCE flag when dropping a disk from an external redundancy diskgroup.

Oracle masala at work:

2008-03-28T17:52:00.000-04:00

Oracle masala at work:
1. How to find Session level parameter value:
2. How to look at OSWatcher(OSW) output in GUI mode:
3. ORACLE_HOME information from oracle data dictionary:
4. Services did not automatically restart after RMAN COLD backup for RAC database:

1.
How to find Session level parameter value:

set serveroutput on size 999999;
DECLARE

i BINARY_INTEGER;
pname v$parameter.name%TYPE;
intval BINARY_INTEGER;
strval v$parameter.value%TYPE;
x BINARY_INTEGER;

BEGIN
pname := 'db_file_multiblock_read_count';
x := dbms_utility.get_parameter_value(pname, intval, strval);

IF x = 0 THEN -- integer or boolean
dbms_output.put_line('IntVal: ' || TO_CHAR(intval));
ELSE
dbms_output.put_line('StrVal: ' || strval);
dbms_output.put_line('IntVal: ' || TO_CHAR(intval));
END IF;
END;
/

or
V$SES_OPTIMIZER_ENV view for optimizer parameters.

2.
How to look at OSWatcher(OSW) output in GUI mode:

a.
Set the PATH and DISPLAY envernoment variables:

$ export PATH=$ORACLE_HOME/jre/1.4.2/bin:$PATH
$ export DISPLAY=xx.xx.xx.xxx:0

b.
Run the oswg.jar file pointing to osw archive directory:

$ java -jar -Xmx256m oswg.jar -i /u00/app/oracle/osw/archive

3.
ORACLE_HOME information from oracle data dictionary:

select substr(file_spec, 1, instr(file_spec, '/', -1, 2) -1) as ORACLE_HOME
from dba_libraries
where library_name = 'DBMS_SUMADV_LIB';

4.
Services did not automatically restart after RMAN COLD backup for RAC database:

Use "host" command in rman script to start the services, to start the services on correct preferred and available nodes
use "sleep" command to start all the instances before starting the services.

HOST '(sleep 180; date; srvctl start service -d ODS;) &';

Which oracle version has what Oracle Compression:

2008-02-27T13:59:00.000-05:00

8i:
Can's:
IOT compression
Unique and non-Unique index compression

Can'ts:
No table compression
No Materialized Views compression
No Bitmap index compression
No LOB compression

Compression occurs only when:
Direct Path SQL*Loader
CREATE TABLE AS SELECT... (CTAS)
Parallel or serial INSERT statement with an APPEND hit

9i:
Can's:
Table compression
Materialized Views compression
IOT compression
Unique and Non-Unique index compression

Can'ts:
Adding a column
Dropping a column
No table compression with more than 255 columns
No Bitmap index compression
No LOB compression

Compression occurs only when:
Direct Path SQL*Loader
CREATE TABLE AS SELECT... (CTAS)
Parallel or serial INSERT statement with an APPEND hit

10g:
Can's:
Adding a column
Table compression
Materialized Views compression
IOT compression
Unique and Non-Unique index compression

Can'ts:
Dropping a column
No table compression with more than 255 columns
No LOB compression (only with utl_compress package)
No Bitmap index compression

Compression occurs only when:
Direct Path SQL*Loader
CREATE TABLE AS SELECT... (CTAS)
Parallel or serial INSERT statement with an APPEND hit

11g:
Can's:
Dropping a column
Adding a column
Table compression (Row-level compression in a block)
Materialized Views compression
IOT compression
Unique and Non-Unique index compression

Can'ts:
No LOB compression (only with utl_compress package)
No Bitmap index compression

Compression occurs only when:
Direct Path SQL*Loader
CREATE TABLE AS SELECT... (CTAS)
Parallel or serial INSERT statement with an APPEND hit

What is Oracle Advanced Compression in 11g?

OLTP compression (Normal DML's also included)
Compression for Unstructured data like documents, spreadsheets and XML files
RMAN compression
Data Pump DATA compression

How to test the performance impact between compress and no compress tables?

CREATE TABLE comp_test_1 (
id NUMBER(10),
data VARCHAR2(50)
)
TABLESPACE users;

CREATE TABLE comp_test_2 (
id NUMBER(10),
data VARCHAR2(50)
)
TABLESPACE users
compress;

SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE
l_loops NUMBER := 100000;
l_data VARCHAR2(50);
l_start NUMBER;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE comp_test_1';
EXECUTE IMMEDIATE 'TRUNCATE TABLE comp_test_2';

l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
INSERT INTO comp_test_1 (id, data)
VALUES (i, 'Data for ' || i);
END LOOP;
DBMS_OUTPUT.put_line('Normal Insert : ' || (DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
INSERT INTO comp_test_2 (id, data)
VALUES (i, 'Data for ' || i);
END LOOP;
DBMS_OUTPUT.put_line('Compressed Insert: ' || (DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
UPDATE comp_test_1
set data = 'VDUMPA'
WHERE id = i;
END LOOP;
DBMS_OUTPUT.put_line('Normal Update : ' || (DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
UPDATE comp_test_2
set data = 'VDUMPA'
WHERE id = i;
END LOOP;
DBMS_OUTPUT.put_line('Uncompressed Update : ' || (DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
SELECT data
INTO l_data
FROM comp_test_1
WHERE id = i;
END LOOP;
DBMS_OUTPUT.put_line('Normal Query : ' || (DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
SELECT data
INTO l_data
FROM comp_test_2
WHERE id = i;
END LOOP;
DBMS_OUTPUT.put_line('Uncompressed Query : ' || (DBMS_UTILITY.get_time - l_start));
END;
/

How to delete jobs stay in 'Running' status in OEM Grid Control?

2008-02-25T14:10:00.001-05:00

Problem Description:
Job stays in 'Running' status, never complete and can't delete from OEM Grid Control.

1. How to find the job_id:

connect sysman@poem
select job_id
from mgmt_job
where job_name = 'BACKUP_ODS_ARCH_2HRS'
and job_owner = 'SYSMAN';

2. How to stop running job:

connect sysman@poem
exec mgmt_job_engine.stop_all_executions_with_id('459582F20C8B6AF4E0403E0ACECC4DCB',TRUE);
commit;

3. How to delete the job:

connect sysman@poem
exec mgmt_job_engine.delete_job('BACKUP_ODS_ARCH_2HRS','SYSMAN');
commit;

Reference:
Note:430626.1
Note:404211.1

ASM access through ftp and html using XDB from Oracle 10gR2:

2008-01-10T17:12:00.000-05:00

1. Set up the Oracle XML DB access to the ASM folders:

sqlplus / as sysdba
@?/rdbms/admin/catxdbdbca 7777 8080

Note:
a. This script takes two parameters (port numbers) for the FTP and HTTP services respectively.

2. Look for the datafiles you want to ftp to other DB:

SQL> select file_name from dba_data_files
2 where tablespace_name = 'USERS';

FILE_NAME
-----------------------------------------------
+ORADB_DBF_DG/oradb/datafile/users.272.583155107

SQL> create tablespace TTS datafile '+ORADB_DBF_DG/oradb/datafile/tts_01.dbf' SIZE 50M;

Tablespace created.

SQL> select file_name from dba_data_files
2 where tablespace_name = 'TTS';

FILE_NAME
--------------------------------------
+ORADB_DBF_DG/oradb/datafile/tts_01.dbf

SQL> alter tablespace tts read only;

Tablespace altered.

SQL> exit

3. How do I access ASM files using ftp:

From the local PC:
C:\ASM_FTP_TEST>ftp
ftp> open atlxd215 7777
Connected to atlxd215.
220- atlxd215
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 atlxd215 FTP Server (Oracle XML DB/Oracle Database) ready.
User (atlxd215:(none)): system
331 pass required for SYSTEM
Password:
230 SYSTEM logged in
ftp> bye

From the server:
atlxd215 | ORADB | /sys
> ftp
ftp> open atlxd215 7777
Connected to atlxd215.
220- atlxd215
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 atlxd215 FTP Server (Oracle XML DB/Oracle Database) ready.
530 Please login with USER and PASS.
530 Please login with USER and PASS.
KERBEROS_V4 rejected as an authentication type
Name (atlxd215:oracle): system
331 pass required for SYSTEM
Password:
230 SYSTEM logged in
Remote system type is Unix.
ftp> bye
221 QUIT Goodbye.
atlxd215 | ORADB | /sys

Note's:
a. All the asm diskgroup directories should be under /sys/asm directory.
b. Use system DB account to login

4. How to access from the browser:

connect / as sysdba
execute dbms_xdb.sethttpport(8080);
execute dbms_xdb.setftpport(7777);
commit;

for FTP type the url : ftp://atlxd215:7777/
for HTTP type the url: http://atlxd215:8080/

Sample Automated Table Compression procedures:

2008-01-08T16:43:00.000-05:00

1. Procedure to compress partition table:

CREATE OR REPLACE procedure compress_tab_par
(tab_name varchar2, own_name varchar2 DEFAULT 'EDW')
is
init_size number;
final_size number;

begin

DBMS_OUTPUT.put_line('Table Partitions compression for ' || own_name || '.' || tab_name || ' started...');
DBMS_OUTPUT.put_line(' ');

select sum(bytes/1024/1024) i_size_MB
into init_size
from user_extents
where segment_name = tab_name;

/* Step1 - Make Indexes partitions Unusable */
begin
for x1 in ( select index_name, partition_name
from user_ind_partitions
where index_name in (select index_name
from user_indexes
where table_name = tab_name
and index_type = 'BITMAP')
and status = 'USABLE')

loop
execute immediate 'ALTER INDEX '||x1.index_name||' MODIFY PARTITION '||x1.partition_name||' UNUSABLE';
end loop;
end;

DBMS_OUTPUT.put_line(' Making Index partitions Unusable COMPLETED.');

/* Step2 - Alter table Partitions to compress */
begin
for x2 in ( select table_name, partition_name
from user_tab_partitions
where table_name = tab_name)
loop
execute immediate 'ALTER TABLE '||x2.table_name||' MODIFY PARTITION '||x2.partition_name||' COMPRESS';
end loop;
end;

DBMS_OUTPUT.put_line(' Modifying Table partitions to compress COMPLETED.');

/* Step3 - Table partitions compress */
begin
for x3 in ( select table_name, partition_name
from user_tab_partitions
where table_name = tab_name)
loop
execute immediate 'ALTER TABLE '||x3.table_name||' MOVE PARTITION '||x3.partition_name||' COMPRESS NOPARALLEL';
end loop;
end;

DBMS_OUTPUT.put_line(' Table partitions compress COMPLETED.');

/* Step4 - Rebuild the Indexes partitions */
begin
for x4 in ( select index_name, partition_name
from user_ind_partitions
where index_name in (select index_name
from user_indexes
where table_name = tab_name)
and status = 'UNUSABLE')
loop
execute immediate 'ALTER INDEX '||x4.index_name||' REBUILD PARTITION '||x4.partition_name||' PARALLEL 8 NOLOGGING';
end loop;
end;

DBMS_OUTPUT.put_line(' Rebuild the Indexes partitions COMPLETED.');

/* Step5 - Analyze the table and indexes */
dbms_stats.gather_table_stats(ownname=> own_name, tabname=> tab_name, estimate_percent => 0.001, method_opt=> 'FOR ALL COLUMNS SIZE 1', cascade=> TRUE, degree=> 8);

DBMS_OUTPUT.put_line('DBMS stats COMPLETED.');

select sum(bytes/1024/1024) f_size_MB
into final_size
from user_extents
where segment_name = tab_name;

DBMS_OUTPUT.put_line(' '||tab_name||' table initial Size in MB is: '||init_size||' MB');
DBMS_OUTPUT.put_line(' '||tab_name||' table final Size in MB is: '||final_size||' MB');
DBMS_OUTPUT.put_line(' NOTE: don''t forget to resize the datafiles !!!!!!!!!!');
DBMS_OUTPUT.put_line('Table Partitions compression for ' || own_name || '.' || tab_name || ' completed.');

EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20101,sqlerrm);
WHEN OTHERS THEN
raise_application_error(-20102,sqlerrm);

end compress_tab_par;
/

2. Procedure to compress Subpartition table:

CREATE OR REPLACE procedure compress_tab_subpar
(tab_name varchar2, own_name varchar2 DEFAULT 'EDW')
is
init_size number;
final_size number;

begin

select sum(bytes/1024/1024) i_size_MB
into init_size
from user_extents
where segment_name = tab_name;

/* Step1 - Making Indexes partitions Unusable */
begin
for x1 in ( select index_name, subpartition_name
from user_ind_subpartitions
where index_name in (select index_name
from user_indexes
where table_name = tab_name))
loop
execute immediate 'ALTER INDEX '||x1.index_name||' MODIFY SUBPARTITION '||x1.subpartition_name||' UNUSABLE';
end loop;
end;

DBMS_OUTPUT.put_line(' Making Index subpartitions Unusable COMPLETED.');

/* Step2 - Alter table to compress */
execute immediate 'ALTER TABLE '||tab_name||' COMPRESS';

DBMS_OUTPUT.put_line(' Altering the table to compress has been COMPLETED.');

/* Step3 - Modifying table partitions compress */
begin
for x2 in ( select table_name, partition_name
from user_tab_subpartitions
where table_name = tab_name)
loop
execute immediate 'ALTER TABLE '||x2.table_name||' MODIFY PARTITION '||x2.partition_name||' COMPRESS NOPARALLEL';
end loop;
end;

DBMS_OUTPUT.put_line(' Modifying table partitions to compress COMPLETED.');

/* Step4 - Table subpartitions compress */
begin
for x3 in ( select table_name, subpartition_name
from user_tab_subpartitions
where table_name = tab_name)
loop
execute immediate 'ALTER TABLE '||x3.table_name||' MOVE SUBPARTITION '||x3.subpartition_name||' NOPARALLEL';
end loop;
end;

DBMS_OUTPUT.put_line(' Table subpartitions compress COMPLETED.');

/* Step5 - Rebuild the Indexes partitions */
begin
for x4 in ( select index_name, subpartition_name
from user_ind_subpartitions
where index_name in (select index_name
from user_indexes
where table_name = tab_name))
loop
execute immediate 'ALTER INDEX '||x4.index_name||' REBUILD SUBPARTITION '||x4.subpartition_name||' PARALLEL 8';
end loop;
end;

DBMS_OUTPUT.put_line(' Rebuild the Indexes partitions COMPLETED.');

/* Step6 - Analyze the table and indexes*/
dbms_stats.gather_table_stats(ownname=> own_name, tabname=> tab_name, estimate_percent => 0.001, method_opt=> 'FOR ALL COLUMNS SIZE 1', cascade=> TRUE, degree=> 8);

DBMS_OUTPUT.put_line('DBMS stats COMPLETED.');

select sum(bytes/1024/1024) f_size_MB
into final_size
from user_extents
where segment_name = tab_name;

DBMS_OUTPUT.put_line(' '||tab_name||' table initial Size in MB is: '||init_size||' MB');
DBMS_OUTPUT.put_line(' '||tab_name||' table final Size in MB is: '||final_size||' MB');
DBMS_OUTPUT.put_line(' Note: Hai don''t forget to resize the datafiles !!!!!!!!!!');

EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20101,sqlerrm);
WHEN OTHERS THEN
raise_application_error(-20102,sqlerrm);

end compress_tab_subpar;
/

3. Procedure to Back out compressed partition table:

CREATE OR REPLACE procedure EDW.nocompress_tab_par
(tab_name varchar2, own_name varchar2 DEFAULT 'EDW')
is
init_size number;
final_size number;

begin

select sum(bytes/1024/1024) i_size_MB
into init_size
from user_extents
where segment_name = tab_name;

/* Step1 - Alter table Partitions to NOcompress */
begin
for x3 in ( select table_name, partition_name
from user_tab_partitions
where table_name = tab_name)
loop
execute immediate 'ALTER TABLE '||x3.table_name||' MOVE PARTITION '||x3.partition_name||' NOCOMPRESS';
end loop;
end;

DBMS_OUTPUT.put_line(' Table partitions NOcompress COMPLETED.');

/* Step2 - Make Indexes partitions Unusable */
begin
for x1 in ( select index_name, partition_name
from user_ind_partitions
where index_name in (select index_name
from user_indexes
where table_name = tab_name
and index_type = 'BITMAP')
and status = 'USABLE')

loop
execute immediate 'ALTER INDEX '||x1.index_name||' MODIFY PARTITION '||x1.partition_name||' UNUSABLE';
end loop;
end;

DBMS_OUTPUT.put_line(' Making Index partitions Unusable COMPLETED.');

/* Step3 - Alter table to NOcompress */
execute immediate 'ALTER TABLE '||tab_name||' NOCOMPRESS';

DBMS_OUTPUT.put_line(' Altering the table to NOcompress has been COMPLETED.');

/* Step4 - Rebuild the Indexes partitions */
begin
for x4 in ( select index_name, partition_name
from user_ind_partitions
where index_name in (select index_name
from user_indexes
where table_name = tab_name)
and status = 'UNUSABLE')
loop
execute immediate 'ALTER INDEX '||x4.index_name||' REBUILD PARTITION '||x4.partition_name||' PARALLEL 8 NOLOGGING';
end loop;
end;

DBMS_OUTPUT.put_line(' Rebuild the Indexes partitions COMPLETED.');

/* Step5 - Analyze the table and indexes */
dbms_stats.gather_table_stats(ownname=> own_name, tabname=> tab_name, estimate_percent => 0.001, method_opt=> 'FOR ALL COLUMNS SIZE 1', cascade=> TRUE, degree=> 4);

DBMS_OUTPUT.put_line('DBMS stats COMPLETED.');

select sum(bytes/1024/1024) f_size_MB
into final_size
from user_extents
where segment_name = tab_name;

DBMS_OUTPUT.put_line(' '||tab_name||' table initial Size in MB is: '||init_size||' MB');
DBMS_OUTPUT.put_line(' '||tab_name||' table final Size in MB is: '||final_size||' MB');

EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20101,sqlerrm);
WHEN OTHERS THEN
raise_application_error(-20102,sqlerrm);

end nocompress_tab_par;
/

ASM SAN migration Case Study:

2008-01-04T13:17:00.000-05:00

Problem:
I need to remove 3 old SAN disks(size of 150 G each) from my ASM database without any down time.

Server, OS and DB details:
OS: RHEL 4 Update 4 (64 bit)
DB: 10.2.0.3.0
Server: DELL PowerEdgeTM 2950(16G Memory, 4 CPU's(Intel(R) Xeon(R) CPU 5160 @ 3.00GHz))

Procedure:
1. Make sure you have free disks available to add to the ASM diskgroup, before dropping the disk's:

On ASM instance:
sqlplus / as sysdba

set linesize 200;
col path format a20;

select NAME, PATH, MOUNT_STATUS, HEADER_STATUS, MODE_STATUS, TOTAL_MB, FREE_MB
from v$asm_disk;

2. Add new ASM disks to the diskgroup:

alter diskgroup ORADB_DATA01_DG add disk 'ORCL:H_1253_1521';
alter diskgroup ORADB_DATA01_DG add disk 'ORCL:H_1253_1522';
alter diskgroup ORADB_DATA01_DG add disk 'ORCL:H_1253_1523';

alter diskgroup ORADB_DATA01_DG REBALANCE POWER 10; or
alter system set asm_power_limit=10 scope=memory;

-- Check the rebalance operation
select * from v$asm_operation;

Time taken to rebalance the diskgroup: app. 90 min.

3. Identify the disks to drop from the diskgroup:

set linesize 200;
col path format a20;

select NAME, PATH, MOUNT_STATUS, HEADER_STATUS, MODE_STATUS, TOTAL_MB, FREE_MB
from v$asm_disk;

ALTER DISKGROUP ORADB_DATA01_DG DROP DISK B_1530_1091;
ALTER DISKGROUP ORADB_DATA01_DG DROP DISK C_1530_1090;
ALTER DISKGROUP ORADB_DATA01_DG DROP DISK D_1530_1081;

alter diskgroup ORADB_DATA01_DG REBALANCE POWER 10; or
alter system set asm_power_limit=10 scope=memory;

-- Check the rebalance operation
select * from v$asm_operation;

Time taken to rebalance the diskgroup: app. 90 min.

4. Physically removing the disks from the server:

as root only!!!

/etc/init.d/oracleasm deletedisk B_1530_1091
/etc/init.d/oracleasm deletedisk C_1530_1090
/etc/init.d/oracleasm deletedisk D_1530_1081

How to find mapping of ASM disks to Physical Devices?

a.
atlxd215 | +ASM | /dev
> /etc/init.d/oracleasm querydisk H_1253_1521
Disk "H_1253_1521" is a valid ASM disk on device [120, 113]

b.
atlxd215 | +ASM | /dev
> ls -l /dev | grep 120 | grep 113
brwxrwx--- 1 oracle dba 120, 113 Oct 1 10:55 emcpowerh1

[or]

atlxd215 | +ASM | /dev
> cd oracleasm
atlxd215 | +ASM | /dev/oracleasm
> cd disks

atlxd215 | +ASM | /dev/oracleasm/disks
> ls -lt
total 0
brw-rw---- 1 oracle dba 120, 177 Oct 1 11:04 L_1253_1561
brw-rw---- 1 oracle dba 120, 161 Oct 1 11:04 K_1253_1560
brw-rw---- 1 oracle dba 120, 145 Oct 1 11:04 J_1253_1541
brw-rw---- 1 oracle dba 120, 129 Oct 1 11:03 I_1253_1540
brw-rw---- 1 oracle dba 120, 113 Oct 1 11:03 H_1253_1521
brw-rw---- 1 oracle dba 120, 97 Oct 1 11:03 G_1253_1520
brw-rw---- 1 oracle dba 120, 81 Oct 1 11:03 F_1253_1501
brw-rw---- 1 oracle dba 120, 65 Oct 1 10:59 E_1253_1500

c.
If you are using multi-path, you will need an additional step to map the physical device to the multi-path device:

as root only!!!

# /sbin/powermt display dev=emcpowerh1

How do I use selected columns with query parameter in Data Pump (Yes, External Tables).

2007-12-31T16:23:00.000-05:00

1. Create a test user and grants:

sqlplus / as sysdba

create user test identified by test
default tablespace users temporary tablespace temp;

grant connect , resource to test;
grant create any directory to test;

2. Create a table and insert some records:

connect test/test

create table cur_objs(obj_id number, obj_name varchar2(128), cdate date, status varchar2(7));

insert into cur_objs
select object_id, object_name, created, status from all_objects;
commit;

insert into cur_objs
select * from cur_objs;
commit;

SQL> select count(1) from cur_objs;

COUNT(1)
----------
69020

SQL> SELECT count(1) FROM cur_objs
WHERE status = 'VALID'
AND obj_name like '%DBMS%';
2 3
COUNT(1)
----------
760

3. Create an Oracle directory and the data pump external table:

-- Check if the directory is valid:
$ cd /backup_ORADB/oracle/ORADB/data_ext
$ ls -lt
total 0
$

SQL> CREATE DIRECTORY data_ext_dir AS '/backup_ORADB/oracle/ORADB/data_ext';

Directory created.

SQL> DROP TABLE extract_cur_objs;

Table dropped.

SQL> CREATE TABLE extract_cur_objs
2 ORGANIZATION EXTERNAL
3 (TYPE ORACLE_DATAPUMP
4 DEFAULT DIRECTORY data_ext_dir
5 LOCATION ('extract_cur_objs1.exp', 'extract_cur_objs2.exp', 'extract_cur_objs3.exp')
6 )
7 PARALLEL 3 REJECT LIMIT 0
8 AS
9 SELECT obj_id, obj_name FROM cur_objs
10 WHERE status = 'VALID'
11 AND obj_name like '%DBMS%';

Table created.

SQL> !ls -lt [eE]*
-rw-r----- 1 oracle dba 24576 Dec 29 14:00 extract_cur_objs1.exp
-rw-r----- 1 oracle dba 12288 Dec 29 14:00 extract_cur_objs3.exp
-rw-r----- 1 oracle dba 16384 Dec 29 14:00 extract_cur_objs2.exp
-rw-r----- 1 oracle dba 41 Dec 29 14:00 EXTRACT_CUR_OBJS_10019.log
-rw-r----- 1 oracle dba 41 Dec 29 14:00 EXTRACT_CUR_OBJS_10021.log
-rw-r----- 1 oracle dba 41 Dec 29 14:00 EXTRACT_CUR_OBJS_10017.log
-rw-r----- 1 oracle dba 41 Dec 29 14:00 EXTRACT_CUR_OBJS_9997.log

SQL>

4. Attaching the files:

DROP TABLE attach_files_cur_objs;

CREATE TABLE attach_files_cur_objs(
obj_id number,
obj_name varchar2(128))
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY data_ext_dir
LOCATION ('extract_cur_objs1.exp', 'extract_cur_objs2.exp', 'extract_cur_objs3.exp')
)
PARALLEL 3 REJECT LIMIT 0;

Side Note: impdp utility may not work with these files.

Database Migration from Sun(File system) to Linux(ASM and RAC)

2007-12-27T12:04:00.000-05:00

a. Create a tablespace MIG_SUN_TO_LINUX and a object:

sqlplus / as sysdba
CREATE TABLESPACE MIG_SUN_TO_LINUX DATAFILE '/u01_ORADB/oradata/ORADB/mig_sun_to_linux_01.dbf' SIZE 100M;

CONNECT ORADBDEV1/xxxxx@ORADB
DROP TABLE EMP;
CREATE TABLE EMP (ENO NUMBER(10), ENAME VARCHAR2(50))
TABLESPACE MIG_SUN_TO_LINUX;

INSERT INTO EMP VALUES(10000001, 'VJ');
INSERT INTO EMP VALUES(10000002, 'ASHRAY');
COMMIT;

b. Use TTS procedure to move the tablesapce files:

sqlplus / as sysdba
EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'MIG_SUN_TO_LINUX', incl_constraints => TRUE);
SELECT * FROM transport_set_violations;

ALTER TABLESPACE MIG_SUN_TO_LINUX READ ONLY;

exp USERID=\"sys/xxxxx@oradb AS SYSDBA\" TRANSPORT_TABLESPACE=y TABLESPACES=MIG_SUN_TO_LINUX FILE=MIG_SUN_TO_LINUX.dmp

scp MIG_SUN_TO_LINUX.dmp atlxd215:/export/home/oracle

c. Use RMAN to convert the datafiles from SUN to LINUX format:

rman target / nocatalog

CONVERT TABLESPACE MIG_SUN_TO_LINUX
TO PLATFORM = "Linux IA (64-bit)"
DB_FILE_NAME_CONVERT = "/u01_ORADB/oradata/ORADB/mig_sun_to_linux_01.dbf" , "/backup_ORADB/oracle/ORADB/mig/mig_sun_to_linux_01.dbf"
PARALLELISM=5;

Side note: If your linux is 32bit? Change TO PLATFORM = "Linux IA (64-bit)" above command to TO PLATFORM = "Linux IA (32-bit)"

d. ftp or scp the MIG_SUN_TO_LINUX tablespace file to destination RAC server(eg:. copy the datafiles under /u00/mig_area/oradb directory):

scp /backup_ORADB/oracle/ORADB/mig/mig_sun_to_linux_01.dbf atlxd215:/u00/mig_area/oradb

e. copy file from disk to ASM using DBMS_FILE_TRANSFER:

-- Create the source and destination directory objects.
CREATE OR REPLACE DIRECTORY sour_db_files_dir AS '/u00/mig_area/oradb';
CREATE OR REPLACE DIRECTORY dest_db_files_dir AS '+ORADB_DATA01_DG/oradb/datafile';

-- Copy the file to ASM disk group.
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'sour_db_files_dir',
source_file_name => 'mig_sun_to_linux_01.dbf',
destination_directory_object => 'dest_db_files_dir',
destination_file_name => 'mig_sun_to_linux_01.dbf');
END;
/

f. Attach the file to target DB (Linux) - imp:

imp USERID=\"sys/xxxxx@oradb AS SYSDBA\" FROMUSER=ORADBDEV1 TOUSER=ORADBTEST1 TRANSPORT_TABLESPACE=y DATAFILES='+ORADB_DATA01_DG/oradb/datafile/mig_sun_to_linux_01.dbf' TABLESPACES=MIG_SUN_TO_LINUX FILE=/export/home/oracle/MIG_SUN_TO_LINUX.dmp

g. Make the tablespace to read write:

ALTER TABLESPACE MIG_SUN_TO_LINUX READ WRITE;

How to convert partition table into Interval Partitioning table in 11g

2007-12-24T23:36:00.000-05:00

1. Create dummy tablespaces and user:
create tablespace testa11_data datafile size 10m autoextend on maxsize 2000m;
create tablespace testa11_index datafile size 10m autoextend on maxsize 2000m;

create tablespace testb11_data datafile size 10m autoextend on maxsize 2000m;

create user vj identified by vj
default tablespace testa11_data;
grant connect, resource to vj;

2. Create a partition table and local index:
connect vj/vj
drop table emp;
create table emp(eno number(1), ename varchar2(10), sal number(6), join_date date)
PARTITION BY RANGE (join_date) (
PARTITION emp_dec06 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY'))
TABLESPACE testa11_data,
PARTITION emp_jan07 VALUES LESS THAN (TO_DATE('01-FEB-2007','DD-MON-YYYY'))
TABLESPACE testa11_data,
PARTITION emp_feb07 VALUES LESS THAN (TO_DATE('01-MAR-2007','DD-MON-YYYY'))
TABLESPACE testa11_data);

create index idx1_eno_emp on emp(eno)
local
tablespace testa11_index;

3. Check and note the partition and tablespace names:
select * from emp;

col table_name for a20;
col partition_name for a20;
col tablespace_name for a20;
col index_name for a20;
select table_name, partition_name, tablespace_name
from dba_tab_partitions
where table_name = 'EMP';

select index_name, partition_name, tablespace_name
from dba_ind_partitions
where index_name = 'IDX1_ENO_EMP';

4. Insert data:
insert into emp values(1,'VJ',100,'20-JAN-2007');
insert into emp values(2,'BJ',200,'20-FEB-2007');
insert into emp values(3,'JJ',300,'20-MAR-2007'); -- this insert will fail, because there is no March data partition.
insert into emp values(4,'PJ',400,'20-APR-2007'); -- this insert will fail, because there is no April data partition.
commit;

5. -- 11g trick
alter table emp set interval(numtoyminterval(1,'MONTH')); -- Setting up the interval for the table.
alter table emp set store in (testb11_data); -- All upcoming partitions data will be assigned to new tablespace (testb11_data).

insert into emp values(3,'JJ',300,'20-MAR-2007');
commit;

insert into emp values(4,'PJ',400,'20-APR-2007');
commit;

select table_name, partition_name, tablespace_name
from dba_tab_partitions
where table_name = 'EMP';

select index_name, partition_name, tablespace_name
from dba_ind_partitions
where index_name = 'IDX1_ENO_EMP';

create tablespace testb11_index datafile size 10m autoextend on maxsize 2000m;

alter index idx1_eno_emp set store in (testb11_index); -- Assigning new tablespace for new index partitions.
insert into emp values(5,'RJ',500,'20-MAY-2007');
commit;

alter index idx1_eno_emp modify DEFAULT ATTRIBUTES tablespace testb11_index;
insert into emp values(6,'RJ',600,'20-JUN-2007');
commit;

Summary: Great 11g partition feature improvement. The thing I like about this is, not only creates new table and index partitions and also allocates existing index tablespace to index partitions. No rebuild index partitions required to modify the correct tablespace!!!