Ashak Ali Khan

CSS Timeout Computation in Oracle Clusterware

2012-02-13T01:13:00.000-08:00

The CSS misscount parameter represents the maximum time, in seconds, that a network heartbeat can be missed before entering into a cluster reconfiguration to evict the node. The following are the default values for the misscount parameter and their respective versions when using Oracle Clusterware* in seconds:
OS 10g (R1 &R2) 11g
Linux 60 30
Unix 30 30
VMS 30 30
Windows 30 30

*CSS misscount default value when using vendor (non-Oracle) clusterware is 600 seconds. This is to allow the vendor clusterware ample time to resolve any possible split brain scenarios.

CSS HEARTBEAT MECHANISMS AND THEIR INTERRELATIONSHIP
The synchronization services component (CSS) of the Oracle Clusterware maintains two heartbeat mechanisms
1.) the disk heartbeat to the voting device and
2.) the network heartbeat across the interconnect which establish and confirm valid node membership in the cluster.
Both of these heartbeat mechanisms have an associated timeout value. The disk heartbeat has an internal i/o timeout interval (DTO Disk TimeOut), in seconds, where an i/o to the voting disk must complete. The misscount parameter (MC), as stated above, is the maximum time, in seconds, that a network heartbeat can be missed. The disk heartbeat i/o timeout interval is directly related to the misscount parameter setting. There has been some variation in this relationship
between versions as described below:
9.x.x.x
NOTE, MISSCOUNT WAS A DIFFERENT ENTITY IN THIS RELEASE
10.1.0.2
No one should be on this version
10.1.0.3
DTO = MC - 15 seconds
10.1.0.4
DTO = MC - 15 seconds
10.1.0.4+Unpublished Bug 3306964
DTO = MC - 3 seconds
10.1.0.4 with CRS II Merge patch
DTO =Disktimeout (Defaults to 200 seconds) Normally OR Misscount seconds only during initial Cluster formation or Slightly before reconfiguration
10.1.0.5
IOT = MC - 3 seconds
10.2.0.1 +Fix for unpublished Bug 4896338
IOT=Disktimeout (Defaults to 200 seconds) Normally OR Misscount seconds only during initial Cluster formation or Slightly before reconfiguration

10.2.0.2
Same as above (10.2.0.1 with Patch Bug:4896338

10.1 - 11.2
During node join and leave (reconfiguration) in a cluster we need to reconfigure, in that particular case we use Short Disk TimeOut (SDTO) which is in all versions SDTO = MC – reboottime (usually 3 seconds)

Misscount drives cluster membership reconfigurations and directly effects the availability of the cluster. In most cases, the default settings for MC should be acceptable. Modifying the default value of misscount not only influences the timeout interval for the i/o to the voting disk, but also influences the tolerance for missed network heartbeats across the interconnect.

Tuning using SQL Tuning advisor

2012-02-07T03:45:00.000-08:00

Tuning for SQL_ID=d86a5g6dm7shd using SQL Tuning advisor
1) Use SQL_TEXT or SQL_ID to define tuning task
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      =>   'd86a5g6dm7shd',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit => 300,
                          task_name   => 'd86a5g6dm7shd_tuning_task',
                          description => 'Tuning task for statement d86a5g6dm7shd.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
2) Execute the tuning task
EXEC DBMS_SQLTUNE.execute_tuning_task( task_name   => 'd86a5g6dm7shd_tuning_task');
3) Check status of tuning task
SELECT task_name, status FROM dba_advisor_log WHERE task_name='d86a5g6dm7shd_tuning_task';
4) Report of tuning task
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('d86a5g6dm7shd_tuning_task') AS recommendations FROM dual;
5) Drop the define tuning task
exec DBMS_SQLTUNE.drop_tuning_task (task_name => 'd86a5g6dm7shd_tuning_task');

Another eg using sql text
1. Create the tuning task:
   declare
   stmt_task VARCHAR2(64);
   begin
   stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => 'select * from service_subscriptions s2 where s2.group_code = ''MEH'' and tss_id not in (''800'', ''93'', ''939'', ''9341'')', task_name => 'SRVC_SUB_TUNING');
   end;
2. Execute the tuning task:

   exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'SRVC_SUB_TUNING');

3. After the execution finishes display result(Connect as system user):
   SET LONG 10000;
   SET PAGESIZE 1000
   SET LINESIZE 200
   SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('SRVC_SUB_TUNING') from dual;

7 Habits of Highly Effective People

2012-02-06T05:24:00.000-08:00

Habit 1: Be Proactive
              The Habit of Choice
Habit 2: BeginWith the End in Mind
               The Habit of Vision
Habit 3: Put First Things First
               The Habit of Integrity and Execution
Habit 4:ThinkWin-Win
               The Habit of Mutual Benefit
Habit 5: Seek First to Understand, Then to Be Understood
               The Habit of Mutual Understanding
Habit 6: Synergize
                The Habit of Creative Cooperation
Habit 7: Sharpen the Saw
                The Habit of Renewal

CVU IPMP Group Consistency Check Failure - 11.2.0.3

2012-02-06T05:19:00.000-08:00


If you install Oracle Grid Infrastructure on a cluster that has Active-Passive IPMP configured, then the prerequisite check for Oracle Solaris IPMP group fail-over consistency may fail.

Solaris IPMP group fail-over consistency check - This is a check to verify the current selection of public and private network classifications is consistent with network interfaces in fail-over dependency of an IPMP group
  Check Failed on Nodes: [mps033,  mps039]  
Verification result of failed node: mps033 
 Details: 
 - 
PRVG-1509 : IPMP fail-over group "PRODUCTION" with interface list "nxge3,nxge7" on node "mps033" has interfaces "nxge7" which are not part of current public network classifications "nxge3/10.64.238.0"  - Cause:  Found an additional fail-over dependency on an interface in an IPMP group which is not classified as a public interface on the identified node.  - Action:  Ensure that all the identified non-participating network interfaces in the IPMP group are classified as public network interface on the identified node. Use command 'oifcfg setif {-node <nodename> | -global} {<if_name>/<subnet>:public}' to classify the network interface as public. 
Back to Top  
Verification result of failed node: mps039 
 Details: 
 - 
PRVG-1509 : IPMP fail-over group "PRODUCTION" with interface list "nxge3,nxge7" on node "mps039" has interfaces "nxge7" which are not part of current public network classifications "nxge3/10.64.238.0"  - Cause:  Found an additional fail-over dependency on an interface in an IPMP group which is not classified as a public interface on the identified node.  - Action:  Ensure that all the identified non-participating network interfaces in the IPMP group are classified as public network interface on the identified node. Use command 'oifcfg setif {-node <nodename> | -global} {<if_name>/<subnet>:public}' to classify the network interface as public.

Workaround: Ignore and continue installation. After installation as root:
If the interface is available on the server, subnet address can be identified by command:
oifcfg iflist
/oracle/sgwgrid/11.2.0.3/bin/crsctl/srvctl modify nodeapps -S '10.64.238.0/255.255.254.0/nxge3|nxge7'
oifcfg setif -global nxge3/10.64.238.0:public
oifcfg setif -global nxge7/10.64.238.0:public

Oracle Multimedia invalid after upgrade from 10.2.0.4 to 11.2.0.3

2012-02-05T00:11:00.001-08:00

Oracle Multimedia invalid after upgrade from 10.2.0.4 to 11.2.0.3

fix:

sqlplus / as sysdba

startup upgrade
/*replace the version 10.2.0.4 in the dbms_registry.loaded
command with the version from which the database has been upgraded
*/

EXECUTE dbms_registry.loaded('ORDIM','10.2.0.4');

spool Oracle_Multimedia.log

@<Oracle_11g_Home>/ord/im/admin/imdbmig.sql

shutdown immediate

startup
?/rdbms/admin/utlrp.sql

check now status should be valid

2012-02-05T00:08:00.000-08:00

while upgrade from 11.1.0.7 to 11.2.0.2
there was slowness in dbua taking more than 15 hours. After truncating SYS.AUD$
and noarchivelog mode , dbua completed in less than a hour.

Some possible reasons which will fix the issue:
- Truncate large SYS.AUD$ or SYS.FGA_LOG$ table
- database in archivelog mode(Upgrade in noarchivelog mode)
- flashback database logging on
- database triggers (i.e. if you do ddl auditing)
- maybe also disable change block tracking

Oracle Integration Migration Book

2011-11-13T22:57:00.000-08:00

Checking dataguard IO Bottleneck

2011-06-06T23:10:00.001-07:00

IO Bottleneck
=============================
At the and of applying a log (log switch) ==> a Standby database instance performs a complete checkpoint
The database instance will update headers of ALL datafiles comprising the database as well as all copies of the control file
in case there is an IO bottle neck ==> this could result in a slow performance of Managed Recovery in the Standby database

Standby: SQL > select process, block#, blocks from v$managed_standby where process='MRP0'

If you see that Managed Recover is stuck at the block# ==> (blocks - 1)
==> this indicated that the Standby has issues related to this bottle neck

cleaning failed oracle clusterware

2010-12-13T04:58:00.000-08:00

How to Clean Up After a Failed 10g or 11.1 Oracle Clusterware Installation [ID 239998.1]

NOTE:942166.1 - How to Proceed from Failed 11gR2 Grid Infrastructure (CRS) Installation

EMC SRDF (Storage ) Replication for 11g DB with ASM on AIX

2010-09-27T23:46:00.000-07:00

EMC SRDF (Storage ) Replication for 11g DB with ASM on AIX
Source :Server1
Asm_dsk001--> hdiskpower111-- >DEVICE ID= 04FD --> It contain say datafiles of DB
Asm_dsk002--> hdiskpower221-- >DEVICE ID= 01FD --> It contain say redofiles of DB

Target: Server2
Asm_dsk001 --> hdiskpower991-->DEVICE ID= 04AC--> It contain say datafiles of DB
Asm_dsk002 --> hdiskpower881 -->DEVICE ID= 01BD--> It contain say redofiles of DB
So in Replication by Storage: Map device ID 04FD= 04AC and 01FD=01BD mirror copy
And create asm character device 1 point to 04AC and 2 to 01BD ie order of character devices
should be maintain same order as source to contain same data else it will throw missing disk error
at mount.

If you do on target as below it would throw missing disk at mount:

Target: Server2
Asm_dsk001 --> hdiskpower881 -->DEVICE ID= 01BD--> It contain say redofiles of DB
Asm_dsk002 --> hdiskpower991-->DEVICE ID= 04AC--> It contain say datafiles of DB

Support Articles ID for Current schedule DB release DB and OCR recovery

2010-09-26T23:48:00.000-07:00

Release Schedule of Current Database Releases refer note [ID 742060.1]

How to restore ASM based OCR after complete loss of the CRS diskgroup [ID 1062983.1]
How To Rename A Diskgroup On ASM 11gR2? [ID 948040.1]
A diskgroup can only be dropped if it can be mounted.
If you cannot mount it anymore (like in your case) the only way to 'delete' the diskgroup is to clean up the involved devices
using OS command 'dd'.

An example for this cleanup task is:

# dd if=/dev/zero of=/dev/ bs=1M count=100

It is sufficient to cleanup the first 100 MB on each affected devices. After all devices are cleaned in that way the diskgroup is not longer existing
and can be re-created using the clean devices.

After the diskgroup is re-created you can restore OCR and database.

ORA-15042: ASM disk "5" is missing from group number "2"

2010-08-29T04:37:00.000-07:00

On 3 nodes 11gR2 RAC, while dismounting and mounting DATA2 resulted in below error on third node that has data2 external redundancy .Alter diskgroup data2 check succeed on node1,node2 that means it does not check ASM disk headers in check command.Analyzing errors in ASM alertlog:

while mounting DATA2 error
NOTE: De-assigning number (2,0) from disk (/dev/asmr5dsk0001)
NOTE: De-assigning number (2,1) from disk (/dev/asmr5dsk0002)
NOTE: De-assigning number (2,2) from disk (/dev/asmr5dsk0003)
NOTE: De-assigning number (2,3) from disk (/dev/asmr5dsk0004)
NOTE: De-assigning number (2,4) from disk (/dev/asmr5dsk0005)
NOTE: De-assigning number (2,5) from disk ()
NOTE: De-assigning number (2,6) from disk (/dev/asmr5dsk0007)
NOTE: De-assigning number (2,7) from disk (/dev/asmr5dsk0011)
NOTE: De-assigning number (2,8) from disk (/dev/asmr5dsk0012)
ERROR: diskgroup DATA2 was not mounted
NOTE: cache deleting context for group DATA2 2/-1905567771
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "5" is missing from group number "2"
ERROR: alter diskgroup DATA2 mount
Wed Aug 25 15:10:48 2010
ASM Health Checker found 1 new failures

It look for Disk /dev/asmr5dsk0006 ,
could be ASM disk header corrupted

To verify:

$ kfed read /dev/asmr5dsk0006 text=name.txt
ERROR!!!, failed to get the oracore error message

$ dd if=/dev/asmr5dsk0006 bs=512 count=1
ةآشءح��sW1+0 records in.
1+0 records out.
$ dd if=/dev/asmr5dsk0005 bs=512 count=1
Kب4#ORCLDISK
DATA2_0004DATA2DATA2_0004��}ىذ��ض��و�)5
��}�l1+0 records in.
1+0 records out.

dd output show DATA2 ASM heading missing
Also verif by
% kfed read /dev/asmr5dsk0006 > mpa002disk5.txt
% kfed read /dev/asmr5dsk0006 > mpa010disk5.txt
% kfed read /dev/asmr5dsk0006 > mpa020disk5.txt

$ kfed read /dev/asmr5dsk0006 > mpa002disk5.txt
$ more mpa002disk5.txt
kfbh.endian: 201 ; 0x000: 0xc9
kfbh.hard: 194 ; 0x001: 0xc2
kfbh.type: 212 ; 0x002: *** Unknown Enum ***
kfbh.datfmt: 193 ; 0x003: 0xc1
kfbh.block.blk: 0 ; 0x004: T=0 NUMB=0x0
kfbh.block.obj: 0 ; 0x008: TYPE=0x0 NUMB=0x0
kfbh.check: 0 ; 0x00c: 0x00000000
kfbh.fcn.base: 0 ; 0x010: 0x00000000
kfbh.fcn.wrap: 0 ; 0x014: 0x00000000
kfbh.spare1: 0 ; 0x018: 0x00000000
kfbh.spare2: 0 ; 0x01c: 0x00000000
ERROR!!!, failed to get the oracore error message

$ kfed read /dev/asmr5dsk0006 > mpa010disk5.txt
$ more mpa010disk5.txt
kfbh.endian: 201 ; 0x000: 0xc9
kfbh.hard: 194 ; 0x001: 0xc2
kfbh.type: 212 ; 0x002: *** Unknown Enum ***
kfbh.datfmt: 193 ; 0x003: 0xc1
kfbh.block.blk: 0 ; 0x004: T=0 NUMB=0x0
kfbh.block.obj: 0 ; 0x008: TYPE=0x0 NUMB=0x0
kfbh.check: 0 ; 0x00c: 0x00000000
kfbh.fcn.base: 0 ; 0x010: 0x00000000
kfbh.fcn.wrap: 0 ; 0x014: 0x00000000
kfbh.spare1: 0 ; 0x018: 0x00000000
kfbh.spare2: 0 ; 0x01c: 0x00000000
ERROR!!!, failed to get the oracore error message
$ ssh mpa020

$ kfed read /dev/asmr5dsk0006 > mpa020disk5.txt
$ more mpa020disk5.txt
kfbh.endian: 201 ; 0x000: 0xc9
kfbh.hard: 194 ; 0x001: 0xc2
kfbh.type: 212 ; 0x002: *** Unknown Enum ***
kfbh.datfmt: 193 ; 0x003: 0xc1
kfbh.block.blk: 0 ; 0x004: T=0 NUMB=0x0
kfbh.block.obj: 0 ; 0x008: TYPE=0x0 NUMB=0x0
kfbh.check: 0 ; 0x00c: 0x00000000
kfbh.fcn.base: 0 ; 0x010: 0x00000000
kfbh.fcn.wrap: 0 ; 0x014: 0x00000000
kfbh.spare1: 0 ; 0x018: 0x00000000
kfbh.spare2: 0 ; 0x01c: 0x00000000
ERROR!!!, failed to get the oracore error message

Confirm , ASM disk header is corrupted so it is not detecting at
mount. ASM always backup disk header automatically. Repair by below
command and mount the disk.

$ kfed repair /dev/asmr5dsk0006

$ kfed read /dev/asmr5dsk0006 > disk5.txt
$ kfed read /dev/asmr5dsk0006 > disk5.txt
$ cat disk5.txt
kfbh.endian: 0 ; 0x000: 0x00
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: 2147483653 ; 0x008: TYPE=0x8 NUMB=0x5
kfbh.check: 1271477282 ; 0x00c: 0x4bc93422
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: ORCLDISK ; 0x000: length=8
kfdhdb.driver.reserved[0]: 0 ; 0x008: 0x00000000
kfdhdb.driver.reserved[1]: 0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]: 0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]: 0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]: 0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]: 0 ; 0x01c: 0x00000000
kfdhdb.compat: 186646528 ; 0x020: 0x0b200000
kfdhdb.dsknum: 5 ; 0x024: 0x0005
kfdhdb.grptyp: 1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts: 3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname: DATA2_0005 ; 0x028: length=10
kfdhdb.grpname: DATA2 ; 0x048: length=5
kfdhdb.fgname: DATA2_0005 ; 0x068: length=10
kfdhdb.capname: ; 0x088: length=0
kfdhdb.crestmp.hi: 32939173 ; 0x0a8: HOUR=0x5 DAYS=0x5 MNTH=0x7 YEAR=0x7da
kfdhdb.crestmp.lo: 2112475136 ; 0x0ac: USEC=0x0 MSEC=0x274 SECS=0x1e MINS=0x1f
kfdhdb.mntstmp.hi: 32940502 ; 0x0b0: HOUR=0x16 DAYS=0xe MNTH=0x8 YEAR=0x7da
kfdhdb.mntstmp.lo: 3052791808 ; 0x0b4: USEC=0x0 MSEC=0x17a SECS=0x1f MINS=0x2d
kfdhdb.secsize: 512 ; 0x0b8: 0x0200
kfdhdb.blksize: 4096 ; 0x0ba: 0x1000
kfdhdb.ausize: 1048576 ; 0x0bc: 0x00100000
kfdhdb.mfact: 113792 ; 0x0c0: 0x0001bc80
kfdhdb.dsksize: 207157 ; 0x0c4: 0x00032935
kfdhdb.pmcnt: 3 ; 0x0c8: 0x00000003
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: 185597952 ; 0x0e0: 0x0b100000
kfdhdb.grpstmp.hi: 32939173 ; 0x0e4: HOUR=0x5 DAYS=0x5 MNTH=0x7 YEAR=0x7da
kfdhdb.grpstmp.lo: 2111597568 ; 0x0e8: USEC=0x0 MSEC=0x31b SECS=0x1d MINS=0x1f
kfdhdb.vfstart: 0 ; 0x0ec: 0x00000000
kfdhdb.vfend: 0 ; 0x0f0: 0x00000000
kfdhdb.spfile: 0 ; 0x0f4: 0x00000000
kfdhdb.spfflg: 0 ; 0x0f8: 0x00000000
kfdhdb.ub4spare[0]: 0 ; 0x0fc: 0x00000000
kfdhdb.ub4spare[1]: 0 ; 0x100: 0x00000000
kfdhdb.ub4spare[2]: 0 ; 0x104: 0x00000000
kfdhdb.ub4spare[3]: 0 ; 0x108: 0x00000000
kfdhdb.ub4spare[4]: 0 ; 0x10c: 0x00000000
kfdhdb.ub4spare[5]: 0 ; 0x110: 0x00000000
kfdhdb.ub4spare[6]: 0 ; 0x114: 0x00000000
kfdhdb.ub4spare[7]: 0 ; 0x118: 0x00000000
kfdhdb.ub4spare[8]: 0 ; 0x11c: 0x00000000
kfdhdb.ub4spare[9]: 0 ; 0x120: 0x00000000
kfdhdb.ub4spare[10]: 0 ; 0x124: 0x0

11gR2 OCR mirroring with External Redundancy

2010-08-22T03:14:00.000-07:00

In case of Normal or High redundancy OCR does not require to be mirror. But if you want
to make OCR mirroring to another diskgroup when external redundancy is used then follow below procedure.

1)ALTER DISKGROUP OCR_VOTING2 SET ATTRIBUTE ‘COMPATIBILITY.ASM’=’11.2’;
2) Connect to the server as root. and execute ocrconfig command from the $GRID_HOME/bin directory.

[root@prddb3 bin]# ./ocrconfig -add +OCR_VOTING2
3)To verify if the new OCR file is created and the /etc/oracle/ocr.loc file is updated with the new file information.

[root@prddb3 bin]# cat /etc/oracle/ocr.loc
The ocrcheck command reflects two OCR locations

[root@prddb3 bin]# ./ocrcheck

..log/hostname/client/ocrconfig_25560.log

root.sh fails while installing 11gR2 Grid onAIX with ASM

2010-07-26T05:41:00.000-07:00

Error:
mpa001:/oracle/brmgrid/11.2.0>./root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oragrid
ORACLE_HOME= /oracle/brmgrid/11.2.0

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n) [n]:
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]:
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]:

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2010-07-26 10:35:27: Parsing the host name
2010-07-26 10:35:27: Checking for super user privileges
2010-07-26 10:35:27: User has super user privileges
Using configuration parameter file: /oracle/brmgrid/11.2.0/crs/install/crsconfig_params
Improper Oracle Clusterware configuration found on this host
Deconfigure the existing cluster configuration before starting
to configure a new Clusterware
run '/oracle/brmgrid/11.2.0/crs/install/rootcrs.pl -deconfig'
to configure existing failed configuration and then rerun root.sh

Solution:
---------------
mpa001:/oracle/brmgrid/11.2.0>/oracle/brmgrid/11.2.0/crs/install/rootcrs.pl -deconfig -force
mpa001:/oracle/brmgrid/11.2.0>/oracle/brmgrid/11.2.0/crs/install/root.sh

---------
Also try to remove .oracle as:
rm -rf /var/tmp/.oracle
rm -rf /tmp/.oracle

Run root.sh

Check ulimit settings are unlimited oracle root& oragrid,oracle users.
July 28, 2010 12:02 AM
Ashak Khan said...
Also try to remove below before root.sh

rm -rf /var/tmp/.oracle
rm -rf /tmp/.oracle

Run root.sh

And check ulimit setting unlimited for oracle,oragrid and root user.

To increase the shell limits:
.
1. Add the following lines to the /etc/security/limits file:
default:
fsize = -1
core = -1
cpu = -1
data = -1
stack = -1
rss = -1
nofiles = -1
core_hard=-1
cpu_hard=-1
data_hard=-1
fsize_hard=-1
stack_hard=-1

2. Enter the following command to list the current setting for the maximum number of process allowed by the Oracle software user:
/usr/bin/lsattr -E -l sys0 -a maxuproc

If necessary, change the maxuproc setting using the following command:

/usr/sbin/chdev -l sys0 -a maxuproc = 16384

3. Repeat this procedure on all other nodes in the cluster.

11R2 RAC ASM disk creation on AIX-IBM

2010-07-25T06:45:00.000-07:00

Oracle ASM need to maintain the SAN disks across all nodes in the ASM cluster but sometimes or always the naming convention is not changeable since it is controlled by the OS kernel. To overcome this issue, we need to create a disk device and link it to the original SAN disk assigned. This disk device created must have same naming in all nodes and linked to the same disk ID in all nodes as well.

Procedure:

1. configure the new SAN disks (assuming EMC)
% cfgmgr

2. Get the IDs and reserve_lock to be No

%lquerypv -h /dev/rdiskpowerXX ( to Check if it is part of Diskgroup)

% chdev -l hdiskpowerXX -a reserve_lock=no

3. Get major/minor number of SAN disk assigned
% ls -l /dev/hdiskpower9
brw-rw---- 1 root system 44, 9 Jun 29 16:12 /dev/hdiskpower9
major number is 44
minor number is 9

4. create the ASM disk which must be a character device
% mknod /dev/asmdisk00XX c 44 9

5. change ownership of the ASM device to be oracle:oinstall
% chown oracle:oinstall /dev/asmdisk00XX

6. change the permission of the ASM disk to be 765
% chmod 765 /dev/asmdisk00XX

7. change ownership of the hdiskpower character device to be oragrid:oinstall
% chown oragrid:oinstall /dev/rhdiskpower9

8. In the other nodes, you must do the same thing with hdiskpower device that have the same ID as hdiskpower9

Database Scripts

2010-03-21T00:57:00.000-07:00

On support.oracle.com, refer Document ID 131704 for all scripts.
Database Scripts Library Index [ID 131704.1]

Checking logical block corruption

2010-01-11T21:45:00.000-08:00

a) $ rman target / nocatalog

b) run {
allocate channel d1 type disk;
backup check logical validate database;
release channel d1;
}

c) select * from V$DATABASE_BLOCK_CORRUPTION ;

d)If V$DATABASE_BLOCK_CORRUPTION contains rows please run this procedure to find the objects that contains the corrupted blocks:

drop table corrupted_objects;
create table corrupted_objects as
select owner, segment_name, partition_name, segment_type, 1 file#, 1 block# from dba_extents where 1=2;
declare
cursor C1 is select file#, block# from v$database_block_corruption;
begin
for r1 in c1 loop
insert into corrupted_objects
select owner, segment_name, partition_name,segment_type, r1.file#, r1.block# from dba_extents where file_id=r1.file#
and r1.block# between block_id and block_id + blocks - 1;
if sql%notfound then /* segment header block might be corrupt causing dba_extents not returning rows. */
insert into corrupted_objects select owner, segment_name, partition_name, segment_type, r1.file#, r1.block#
from dba_segments where header_file=r1.file# and header_block = r1.block#;
end if;
end loop;
end;
/

e) Select all the objects populated in the table corrupted_objects:

SQL> select distinct owner, segment_name, partition_name, segment_type from corrupted_objects;

restarting resource to clear OFFLINE State

2009-09-27T00:32:00.000-07:00

$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.EPPROD.db application ONLINE ONLINE jury
ora....p1.inst application ONLINE ONLINE jasmine
ora....p2.inst application ONLINE ONLINE jury
ora....NE.lsnr application ONLINE ONLINE jasmine
ora....ine.gsd application ONLINE ONLINE jasmine
ora....ine.ons application ONLINE ONLINE jasmine
ora....ine.vip application ONLINE ONLINE jasmine
ora....RY.lsnr application ONLINE ONLINE jury
ora.jury.gsd application ONLINE OFFLINE
ora.jury.ons application ONLINE OFFLINE
ora.jury.vip application ONLINE ONLINE jury

$ crs_stat
NAME=ora.EPPROD.db
TYPE=application
TARGET=ONLINE
STATE=ONLINE on jury
..
..
..

NAME=ora.jury.LISTENER_JURY.lsnr
TYPE=application
TARGET=ONLINE
STATE=ONLINE on jury

NAME=ora.jury.gsd
TYPE=application
TARGET=ONLINE
STATE=OFFLINE

NAME=ora.jury.ons
TYPE=application
TARGET=ONLINE
STATE=OFFLINE

NAME=ora.jury.vip
TYPE=application
TARGET=ONLINE
STATE=ONLINE on jury

$ crs_stop ora.jury.gsd
Target set to OFFLINE for `ora.jury.gsd`
$ crs_start ora.jury.gsd
Attempting to start `ora.jury.gsd` on member `jury`
Start of `ora.jury.gsd` on member `jury` succeeded.
$ crs_stop ora.jury.ons
Target set to OFFLINE for `ora.jury.ons`
$ crs_start ora.jury.ons
Attempting to start `ora.jury.ons` on member `jury`
Start of `ora.jury.ons` on member `jury` succeeded.
$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.EPPROD.db application ONLINE ONLINE jury
ora....p1.inst application ONLINE ONLINE jasmine
ora....p2.inst application ONLINE ONLINE jury
ora....NE.lsnr application ONLINE ONLINE jasmine
ora....ine.gsd application ONLINE ONLINE jasmine
ora....ine.ons application ONLINE ONLINE jasmine
ora....ine.vip application ONLINE ONLINE jasmine
ora....RY.lsnr application ONLINE ONLINE jury
ora.jury.gsd application ONLINE ONLINE jury
ora.jury.ons application ONLINE ONLINE jury
ora.jury.vip application ONLINE ONLINE jury
$

Oracle Performance Diagnostic Guide

2009-09-06T05:13:00.000-07:00

Oracle Performance Diagnostic Guide (OPDG)

--> MetaLink Doc ID: 390374.1

Configure ssh for RAC using rsa/dsa

2009-07-20T05:11:00.001-07:00

From each node say md004a,md004b, logged in as oracle:

1) Execute below on md004a,md004b
mkdir ~/.ssh
chmod 755 ~/.ssh
/usr/bin/ssh-keygen -t rsa
/usr/bin/ssh-keygen -t dsa

2) On md004a
cat ~/.ssh/id_rsa.pub > ~/.ssh/authorized_keys
cat ~/.ssh/id_dsa.pub > ~/.ssh/authorized_keys
ssh oracle@md004b cat ~/.ssh/id_rsa.pub > ~/.ssh/authorized_keys
ssh oracle@md004b cat ~/.ssh/id_dsa.pub > ~/.ssh/authorized_keys
chmod 644 ~/.ssh/authorized_keys

3) on md004b
ssh oracle@md004a cat ~/.ssh/id_rsa.pub > ~/.ssh/authorized_keys
ssh oracle@md004a cat ~/.ssh/id_dsa.pub > ~/.ssh/authorized_keys
chmod 644 ~/.ssh/authorized_keys

4) on md004a and md004b
cp id_rsa.pub authorized_keys2

Common RAC errors

2009-07-04T23:28:00.000-07:00

oui-10009
PRKP-1037 : Failed to create cluster Database
CRS-0210: Could not find resource
CRS ownership - CRS did not start after node reboot
clsssInitNative: connect failed, rc 9,Received status 3 from CSS
Cluster Split based on the service name

oui-10009

This error occured while adding a new node in the existing cluster. The following workaround is used as follows:

Node 1:

cd $ORACLE_HOME/oui/bin

./addNode.sh -silent "CLUSTER_NEW_NODES={new nodename}" -logLevel trace –debug

You may want to browse "Node add" to get more information.

Node add

PRKP-1037 : Failed to create cluster Database

Step 1:

[oracle@orcl-experts.info bin]$ ./crs_stat
NAME=ora.SAAP.db
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE

Step 2 : crs_unregister ora.SAAP.db

Step 3: srvctl add database -d SAAP -o /data01/app/oracle/product/10.2.0/db/

[oracle@sabharwal11 bin]$ srvctl add instance -d SAAP -i SAAP1 -n sabharwal11
[oracle@sabharwal11 bin]$ srvctl add instance -d SAAP -i SAAP2 -n sabharwal12
[oracle@sabharwal11 bin]$ srvctl add instance -d SAAP -i SAAP3 -n sabharwal13

[oracle@sabharwal11 bin]$ srvctl stop database -d SAAP
[oracle@sabharwal11 bin]$ srvctl start database -d SAAP
[oracle@sabharwal11 bin]$ ./crs_stat
NAME=ora.SAAP.SAAP1.inst
TYPE=application
TARGET=ONLINE
STATE=ONLINE on sabharwal11

NAME=ora.SAAP.SAAP2.inst
TYPE=application
TARGET=ONLINE
STATE=ONLINE on sabharwal12

NAME=ora.SAAP.SAAP3.inst
TYPE=application
TARGET=ONLINE
STATE=ONLINE on sabharwal13

[oracle@sabharwal11 bin]$ srvctl status database -d SAAP
Instance SAAP1 is running on node sabharwal11
Instance SAAP2 is running on node sabharwal12
Instance SAAP3 is running on node sabharwal13

CRS-0210: Could not find resource

[oracle@orcl-experts1.com admin]$ srvctl stop nodeapps -n orcl-experts1.com

CRS-0210: Could not find resource 'ora.orcl-experts1.com.LISTENER_orcl-experts1.com.lsnr'.

[oracle@orcl-experts1.com admin]$ srvctl start nodeapps -n orcl-experts1.com

CRS-0210: Could not find resource ora.orcl-experts1.com.LISTENER_orcl-experts1.com.lsnr.

[oracle@orcl-experts1.com bin]$ ./crs_register ora.orcl-experts1.com.LISTENER_orcl-experts1.com.lsnr

[oracle@orcl-experts1.com bin]$ srvctl start nodeapps -n orcl-experts1.com
[oracle@orcl-experts1.com bin]$ srvctl stop nodeapps -n orcl-experts1.com
[oracle@orcl-experts1.com bin]$ srvctl start nodeapps -n orcl-experts1.com

NO Errors

CRS ownership - CRS did not start after node reboot - Metalink 428195.1

I had to deal with a maintenance which required disabling the auto start of CRS during server reboot:

#/etc/init.d/initcrs disable

I shutdown the RAC 2 node cluster and verified that there is no oracle process running and hand over the servers to Sysadmin. After the hardware maintenance before the 2nd reboot the sysadmin asked me to re enable the CRS auto start. In both nodes I did:

#/etc/init.d/initcrs enable

Sysadmin rebooted both the nodes. After 10 minutes I checked the process details and found that CRS stack is not running and the instances are not up. We had a deadline of 1 hour to bring up all the instances. Under the short time and pressure I started digging out the issue of why the CRS stack was not up. I tried to bring it up manually but it did not work.

I found that after remounting the file systems the permission of /oracle01 mount point (voting file and OCR file location) is:

-rw-r--r-- 1 root root 10240000 Apr 3 21:41 voting.dbf

Sysadmin and I decided to make an immediate change:

chown -R root:install /oracle01

and

we rebooted the machine.

It worked and during the node reboots process all the CRS and Oracle processes came up.

The maintenance was based on the mounting the existing file system on the new LUN.

clsssInitNative: connect failed, rc 9,Received status 3 from CSS. Waiting for good status .., Failure at final check of Oracle CRS stack. 10

Issue: I had issue while bringing up CRS in the 2nd node.The root.sh executions failed with the following message:

Startup will be queued to init within 90 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.

Failure at final check of Oracle CRS stack.
10

crsd.log

2007-10-26 17:33:37.510: [ CSSCLNT][3086960320]clsssInitNative: connect failed, rc 9

2007-10-26 17:33:37.511: [ CRSRTI][3086960320]0CSS is not ready. Received status 3 from CSS. Waiting for good status ..

2007-10-26 17:33:38.545: [ COMMCRS][116607920]clsc_connect: (0x8605510) no listener at (ADDRESS=(PROTOCOL=ipc)(KEY=OCSSD_LL_oracl-experts.info2_crs))

Investigation:

I looked into the files under /var/tmp/.oracle in node1 - Looks everything fine but in the 2nd node , the CRS process was not able to write

Solution:

After long research, I found out that there is a major issue with the /var/tmp

[root@oracl-experts.info2 app]# chmod 1777 /var/tmp

[root@oracl-experts.info2 app]# ls -ld /var/tmp

drwxrwxrwt 3 root root 4096 Oct 26 17:33 /var/tmp

total 8

drwxrwxrwt 3 root root 4096 Oct 26 17:33 ..
srwxrwxrwx 1 oracle oinstall 0 Oct 26 17:52 soracl-experts.info2DBG_CSSD
srwxrwxrwx 1 oracle oinstall 0 Oct 26 17:52 sOracle_CSS_LclLstnr_crs_2
srwxrwxrwx 1 oracle oinstall 0 Oct 26 17:52 sOCSSD_LL_oracl-experts.info2_crs
srwxrwxrwx 1 root root 0 Oct 26 17:52 sprocr_local_conn_0_PROC
srwxrwxrwx 1 oracle oinstall 0 Oct 26 17:52 soracl-experts.info2DBG_EVMD
srwxrwxrwx 1 root root 0 Oct 26 17:52 soracl-experts.info2DBG_CRSD
srwxrwxrwx 1 oracle oinstall 0 Oct 26 17:52 sSYSTEM.evm.acceptor.auth
srwxrwxrwx 1 oracle oinstall 0 Oct 26 17:52 sCoracl-experts.info2_crs_evm
srwxrwxrwx 1 oracle oinstall 0 Oct 26 17:52 sAoracl-experts.info2_crs_evm
srwxrwxrwx 1 root root 0 Oct 26 17:52 sora_crsqs
srwxrwxrwx 1 root root 0 Oct 26 17:52 sCRSD_UI_SOCKET
drwxrwxrwt 2 root root 4096 Oct 26 17:52 .

[root@oracl-experts.info2 app]# ./root.sh
WARNING: directory '/data01/crs/oracle/product/10.2.0' is not owned by root
WARNING: directory '/data01/crs/oracle/product' is not owned by root
WARNING: directory '/data01/crs/oracle' is not owned by root
WARNING: directory '/data01/crs' is not owned by root
Checking to see if Oracle CRS stack is already configured

Setting the permissions on OCR backup directory

Setting up NS directories

Oracle Cluster Registry configuration upgraded successfully

WARNING: directory '/data01/crs/oracle/product/10.2.0' is not owned by root

WARNING: directory '/data01/crs/oracle/product' is not owned by root

WARNING: directory '/data01/crs/oracle' is not owned by root

WARNING: directory '/data01/crs' is not owned by root

clscfg: EXISTING configuration version 3 detected.

clscfg: version 3 is 10G Release 2.

assigning default hostname oracl-experts.info1 for node 1.

assigning default hostname oracl-experts.info2 for node 2.

Successfully accumulated necessary OCR keys.

Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.

node :

node 1: oracl-experts.info1 oracl-experts.info1-priv oracl-experts.info1

node 2: oracl-experts.info2 oracl-experts.info2-priv oracl-experts.info2

clscfg: Arguments check out successfully.

NO KEYS WERE WRITTEN. Supply -force parameter to override.

-force is destructive and will destroy any previous cluster

configuration.

Oracle Cluster Registry for cluster has already been initialized

Startup will be queued to init within 90 seconds.

Adding daemons to inittab

Expecting the CRS daemons to be up within 600 seconds.

CSS is active on these nodes.

oracl-experts.info1

oracl-experts.info2

CSS is active on all nodes.

Waiting for the Oracle CRSD and EVMD to start

Oracle CRS stack installed and running under init(1M)

Running vipca(silent) for configuring nodeapps

The given interface(s), "eth0" is not public. Public interfaces should be used t o configure virtual IPs.

Cluster Split based on the service name

Cluster split - 2 nodes for DSS and 1 Node for OLTP

The requirement is to allocate 1 node exclusively to OLTP enviroment out of 3 node cluster.

The logic is based on the setting up the service_name

SQL> show parameter service_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orclexperts.info
SQL>

SQL> alter system set service_names='OLTP_experts' sid='ORCLEXPERTS3';

System altered.

SQL>
[oracle@s262381ch3el101 ~]$ lsnrctl services | grep -i orclexperts
Service "ORCLEXPERTS.info" has 3 instance(s).
Instance "ORCLEXPERTS1", status READY, has 2 handler(s) for this service...
Instance "ORCLEXPERTS2", status READY, has 1 handler(s) for this service...
Instance "ORCLEXPERTS3", status READY, has 1 handler(s) for this service...
Service "ORCLEXPERTSXDB.info" has 3 instance(s).
Instance "ORCLEXPERTS1", status READY, has 1 handler(s) for this service...
Instance "ORCLEXPERTS2", status READY, has 1 handler(s) for this service...
Instance "ORCLEXPERTS3", status READY, has 1 handler(s) for this service...
Service "ORCLEXPERTS_XPT.info" has 3 instance(s).
Instance "ORCLEXPERTS1", status READY, has 2 handler(s) for this service...
Instance "ORCLEXPERTS2", status READY, has 1 handler(s) for this service...
Instance "ORCLEXPERTS3", status READY, has 1 handler(s) for this service...
Service "OLTP_ORCLEXPERTS.info" has 1 instance(s).
Instance "ORCLEXPERTS3", status READY, has 1 handler(s) for this service...

[oracle@experts1 admin]$ sqlplus system/xxxx@OLTP_ORCLEXPERTS

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 6 22:35:44 2008

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL>

SQL> show parameter service_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string OLTP_ORCLEXPERTS, ORCLEXPERTS.info

TNS:
OLTP_ORCLEXPERTS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orclexperts3-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = OLTP_ORCLEXPERTS.info)
(INSTANCE_NAME = ORCLEXPERTS3)
)
)

All OLTP connection strings changed to use the above string value.

Effective Leadership Tips

2009-05-03T01:23:00.000-07:00

1. Be a leader, not a victim: Do not blame others or look for scapegoats. Your ability to be a leader starts with mastering yourself.

2. Beware of your blind spots: The key to teamwork is valuing others’ perspectives, and encouraging them to fill in where you have gaps.

3. Take care of important relationships: Treat others as though they are the most important thing. A Chinese proverb says: If you want one year’s prosperity, grow grain. If you want ten years’ prosperity, grow trees. If you want 100 years’ prosperity, grow people.

4. Balance short- and long-term results: The ‘golden egg’ or the ‘golden goose;’ which do you protect? Both, but with balance.

5. Know and live your mission: Create a personal mission statement that is short, memorable, passionate, measurable and for which you become accountable.

6. Do what matters most – now: Do things that will make your personal life more meaningful, and do what it takes to move to a higher level of professional success.

7. Be a master communicator: Practice the 2:1 rule; listen twice as much as you speak. From listening comes wisdom.

8. Be a great team player: When you have disagreements or conflicts with others, try this approach: “you see it differently, help me understand.”

9. Commit one hour per day to personal renewal: Take time for spiritual, physical, and professional growth. A great leader takes care of him/her self.

10. Live with integrity: Live and lead in honesty and forthrightness; be true to your word.

5 Tips on Effective Leadership

2009-05-03T01:17:00.000-07:00

Communicate clearly and routinely. Lay out your company goals and principles in a mission statement and keep sharing your vision with your employees.

Involve employees in setting objectives. Give them feedback on how they are progressing toward meeting those targets.

Give your people authority, then hold them accountable. But don’t go after them personally when things go wrong. Find out first if the process is at fault.

Be accountable yourself. Install an advisory board or executive team to help you make good strategic decisions and give you feedback on your own performance.

Be trustworthy and extend trust to your employees. That will help you earn their loyalty and strengthen your company

Oracle 11g New Features

2009-05-03T00:17:00.000-07:00

R M A N
Active Database Duplication or Network-Aware database duplication
No need to backup for duplication. Doesn't copy and apply online redo log, only up to most recent archive. Both password file required with same password.
Flash Recovery Area is not copied. Archive log are copied if necessary.
Source may be in mount or open(archivelog mode). Can automatically copy if specify the clause.
duplicate database to dupdb from active database db_file_name_convert '/u01', '/v01' SPFILE parameter_value_convert '/u01', '/v01'
set log_file_name_convert '/u01', '/v01' SET sga_max_size '3000m' set sga_target '2000m'
SET=> SET are processed later, does not specify db_file_name_convert
SPFILE => SPFILE must be in use in source instance, it will be copied on destination and used to start auxiliary instance. Destination database must be
started using a text init.ora containing only DB_NAME parameter.
db_file_name_convert=> replace pattern for datafiles and tempfiles
NOFILENAMECHECK=> databases are on different hosts, no need to check for different filenames
PASSWORD FILE=> if specified will copy source password file to destination
1)-Create an init.ora for auxiliary instance
2)-Create a passwordfile for auxiliary instance with same SYSDBA password as the source(target)
3)-startup auxiliary instance using startup nomount
4)-Connect RMAN to target and to auxiliary instance, and issue the active database duplication command

Parallel Backup or Multisection backup
Parallelize datafiles backup up to 265 parts(manually customizable for each datafile), multiple channel recommended.
All section have same size may be except the last. SECTION SIZE mandatory and value must be specified. Can't use MAXPIECESIZE with SECTION SIZE.
SECTION SIZE may be specified using VALIDATE DATAFILE command. Each section is a backup piece in the backup set.
backup SECTION SIZE 200m tablespace pippo;
* Views *
V$BACKUP_DATAFILE and RC_BACKUP_DATAFILE (piece, section_size, multi_section[yes, no])

Archival(Long-Term) Backup - KEEP option
Are backed up control file, spfile, data and archived redo logs.
LOGS/NOLOGS clause can't be specified with KEEP. Self-contained archival backup immune from any retention. Redo logs are switched, current needed.
Store archival backup in non flash recovery area, may fill up quickly. Recovery catalog required if using KEEP FOREVER.
CHANGE not allowed for backup in flash recovery area.
An archived log is never made obsolete if needed for a guaranteed restore point, may be obsolete if needed for a normal restore point.
backup database format 'c:\arch\db_%U.bkp' tag quarterly KEEP until time 'sysdate + 365' RESTORE POINT firstq08;
#KEEP=>[FOREVER, UNTIL TIME], recovery catalog required if using KEEP FOREVER.
#RESTORE POINT=>SCN is captured when backup finish. The time until which RMAN can restore an archival backup
CHANGE backup tag 'pippo_bck' forever;
change copy of database controlfile keep;

Fast Incremental Backup
Flashback Database feature is required, searches flashback logs before backup files. Only corrupted blocks are restored and recovered in a datafile.
Archivelog mode always required. Db mounted or open. No proxy copies allowed. Block change tracking may be enabled on physical standby db.
10g blockrecover(no more available)-> 11g recover...block
recover datafile 2 block24 datafile 10 block 21 from tag=sundaynight;
RMAN>recover corruption list;
#recover all corrupted blocks from listed in V$DATABASE_BLOCK_CORRUPTION, view data is then deleted
* Identified Corrupt Blocks *
analyze table, analyze index, list failure, validate(populates V$DATABASE_BLOCK_CORRUPTION), backup...validate, export to /dev/null, dbverify
* Views *
V$DATABASE_BLOCK_CORRUPTION list block corrupted, populated by VALIDATE DATABASE

RMAN Compression
BZIP2(good compression) and ZLIB(def. faster 45%)
RMAN>configure compression algorithm 'bzip2';

Archived Redo Log Deletion
configure archivelog deletion policy=NONE(def.)(applies to all archived logs stored on disk including Flash Recvoery area)(Archived log will be marked
for deletion after transfered to all log_archive_dest_n and backed up or be obsolete)
configure archivelog deletion policy to backed up 2 times to sbt

RMAN Catalog merging - Export/Import RMAN catalog
source will be deleted and deregistered(unless you use ), source db version same as RMAN client version,
fails if same db is already registered in destination
import catalog rman/rman@reporman dbid=122,123 db_name=pippo,pluto dbid=121,31;
#you are connected to destination, rman@reporman will be imported

Virtual private catalog or Virtual catalog
1) SYSDBA> create the db user
2) SYSDBA> grant recovery_catalog_owner to user
3) real CATALOG OWNER> grant [REGISTER_DATABASE | REGISTER] to user;
#permits to register unknown databases, does not grant recovery catalog access
and / or
real CATALOG OWNER>grant catalog for database t1 to user;
#grant access to a target existing database to a user even if db is not yet registered, use DBID if db not yet registered.
#User may un/register databases. User creates local scripts and read global scripts
5) VIRTUAL USER - RMAN> create virtual catalog;
#use for 11g or later database
or
VIRTUAL USER - RMAN> DBMS_RCVCAT.create_virtual_catalog
#use for 10g database
6) VIRTUAL USER - RMAN> register database;
RMAN>revoke all from privileges from user;
* Packages *
DBMS_RCVCAT.create_virtual_catalog
#must execute before working with any pre-11.1 database. Does not create a virtual private catalog

Data Recovery Advisor
<- RMAN, EM Support Workbench & Database Control, Healt Monitor(following errors), VALIDATE or BACKUP command
Quickly detect and analyzes data failures, repair manual(mandatory, optional) or automatic, data loss or no
Automatically repair some network I/O errors, control file and datafile inconstistencies, accidental renaming of datafiles. No RAC. No standby database.
No ORA-600, must be in Healt Monitor
status(open, closed), priority(critical, high, low), grouping
1) list failure
#failures are listed by priority [1:CRITICAL, 2:HIGH, 3:LOW], failures with same priority are ordered cronologically
2) advise failure
#generate manual and automated options for repair, rman scripts are generated for automated options
3) repair failure
#by default high and critical failures will be repaired and closed. Generates warning if new failures are detected since last
May not automatically close a fixed failure
* Views *
V$IR_FAILURE or list failure; change failure 7 closed; change failure priority low;
#failures are listed by priority [1:CRITICAL, 2:HIGH, 3:LOW], failures with same priority are ordered cronologically
V$IR_MANUAL_CHECKLIST or advise failure;
#summary and close fixed
V$IR_REPAIR or repair failure;
#requires advise failure
validate database;
#checks only for intrablock(no interblock) corruption in all datafiles, control files and the server parameter file
validate database;
#proactive check

Healt Monitor
-> run Data Recovery Advisor checks
View Healt Monitor checker reports using: EM, ADRCI, DBMS_HM, V$HM_RUN
Healt check mode can be:
[Reactive]: Healt check ran automatically when a critical error occurred
[Manual]
Available checks:
[Data Block Integrity] : check disk image block corruptions, checksum failures and intrablock corruptions. Not check for interblock corruption
[Db Structure Integrity]: verify accessibility, consistency and corruption of all database files
[Undo Segment Integrity]: check for logical Undo corruptions and attempt to repair them
[Redo Integrity check], [Dictionary Integrity check]
* Packages *
DBMS_HM.run_check(check_name, run_name, input_params)
DBMS_HM.(GET)RUN_REPORT(run_name M, type O , level O )
* Views *
V$HM_CHECK(all possible checks), V$HM_RUN(view HM checker reports), V$HM_CHECK_PARAM, V$HM_FINDING, V$HM_RECOMMENDATION
show hm_run;
#show all checks

Partitioning
See http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-partitioning.html
Interval partitioning
Limitation
Cannot be used on subpartitions.
When used in primary partitions with subpartitions, only a single subpartition is created accepting DEFAULT values
The high value of the range partitions is called the Transition Point
Extends range partitioning by automatically creating new partitions on new data insert out of bounds. Only a single column DATE or NUMBER.
Works on index-organized tables. You can't create domain indexes
create table pippo(time_id date) partition by range(time_id)
interval(numtoyminterval(1, 'month'))
(partition p0 values less than to_date('3/12/1976', 'dd/mm/yyyy')),
(partition p1 values less than to_date('3/01/1977', 'dd/mm/yyyy'))
alter table pippo merge partitions for(to_date('3/12/1976', 'dd/mm/yyyy')), to_date('3/01/1977', 'dd/mm/yyyy')))
#the partitions to merge must be adiacent
* Views *
DBA_TAB_PARTITIONS(partitioning_type[range, interval(new automatically created)])

System partitioning
The application must explicitly name the partition in the insert/merge statements, not required for update/delete.
Can't create as CTAS, Hurts performances. No keys. Partitions can have different physics attributes. Can't create unique local indexes. Can't split partitions
create table pippo(c1 number) partition by system (partition p1, partition p2)
insert into pippo partition(pippo) values(1)
alter table pippo merge partitions p1, p2 into partition p1

Virtual Column-Based partitioning
Virtual column is computed on the fly. Datatype is optional. Can collect optimizer statistics. You can index a virtual column.
Can use a virtual column in all dml/ddl. Can partition over a virtual column. Can create virtual columns only on normal heap table.
Can't refers another virtual column. Output must always be scalar no lob, long, raw, ... Can't use pl/sql function if used for partitioning
create table pippo (c1 number, c2 number generated always as (c1*2))

Reference partitioning
Master table is a partitioned table. Can't use interval partitioning. Requires foreign key relationship.
If no tablespace specified then partitions are in same tbs of parent table's partition. Can't specify partitions bounds.
Partition names must be unique between master and reference table. Can't disable the foreign key.
Can't add or drop partitions from child table while its possibly for parent table.
Pruning and partitionwise joins works even if query predicates are different from the partitioning key

create table customers (
cust_id number primary key,
cust_name varchar2(200),
rating varchar2(1) not null)
partition by list (rating)(partition pA values ('A'), partition pB values ('B'));

create table sales (
sales_id number primary key,
cust_id number not null,
sales_amt number,
constraint fk_sales_01 foreign key (cust_id) references customers)
partition by reference (fk_sales_01);

Composite Partitioning
You can create composite partitions in any combination.
Range-range Range-hash Range-list List-range List-hash List-list
Directory structure
$ORACLE_BASE (recommended only)
$ORACLE_HOME (do not explicitly set)
dbs
oranfstab sfirst default location
Flash recovery area (different disk then datafiles)
Datafiles (different disk then Flash Recovery Area)
Inventory
/diag Advanced Diagnostic Repository (ADR base=diagnostic_dest multiple ADR homes allowed but only one ADR base)
Db name
Instance name (use v$DIAG_INFO to view these paths) ADR_HOME (multiple ADR homes allowed but only one ADR base)
alert (xml alert format)
hm (checker reports)
incident
trace(V$DIAG.name='Diag Trace')
text alert.log
background process trace files
foreground process trace files
wallet
ewallet.p12
cdump
bdump
udump
...

init.ora new params
# A S M
asm_preferred_read_failure_groups=data.locationA, data.locationB #Dynamic. disk_group_name.failure_group only disks local to the instance!!
asm_diskgroups
asm_diskstring
# M E M O R Y
sga_target=0 #Static. Enable Automatic Memory Management
pga_aggregate_target=0 #Static. Enable Automatic Memory Management
memory_target=900M #dynamic default 0 and when upgrading. Total memory for SGA and PGA. With sga_target=0 and pga_target=0 then defaults 60% to SGA and 40% to PGA
#With sga_target!=0 and pga_target!=0 they indicate minimum values
memory_max_target=2000M; #if unset defaults to memory_target(if greater than 0) or 0(if memory_target is not set)
# R E S U L T C A C H E
result_cache_max_size #Static. Set the high limit. 0 disable. Maximum is 0.25% of memory_target, 0.5% of sga_target, 1%><75% of shared pool.
result_cache_max_result #Def. 5, max 100. Max % of result cache for a single cached result
result_cache_remote_expiration #Def. 0. Time for a remote cached result to be valid. Positive values may lead to uncorrect results
result_cache_mode MANUAL(def.), FORCE #Dynamic. MANUAL works only with /*+ result_cache*/ hint on SQL. Same behaviour for client cache.
#FORCE cache all unless /*+ no_result_cache */
client_result_cache_size #Def. 0 disabled. Static. Size of client cache in bytes. Overriden by oci_result_cache_max_size
client_result_cache_lag #Set a low value for unfrequent accesses
# client sqlnet.ora only
#oci_result_cache_max_size #Overrides client_result_cache_size
#oci_result_cache_max_rset_size #set max size in bytes of a single SQL result for a process
#oci_result_cache_max_rset_rows #set max size in rows of a single SQL result for a process
# P L / S Q L
plsql_optimize_level=3 #default is 2 recommended 3. 2 is the minimum level to enable native compilation
plsql_code_type=INTERPRETED(def.), NATIVE #dynamic. INTERPRETED: Pl/sql is precompiled to Pl/sql bytecode using C compiler.
#NATIVE db compile Pl/sql to machine code, no need of interpreter, DLL are stored and loaded in db catalog, no filesystem involved
# S E C U R I T Y
sec_case_sensitive_logon=TRUE(def.), FALSE - case sensitive passwords
sec_max_failed_login_attempts=5 #after 5 attempts the connection will be dropped
sec_protocol_error_further_action=[CONTINUE | DROP | DELAY], 5 #CONTINUE allow the connection to continue DROP terminate the client connection after 5 attempts
#DELAY delay client connection for 5 seconds
sec_protocol_error_trace_action=none, trace, log, alert #TRACE log to user trace, LOG log to alert.log
audit_trail=default is DB #Audit is enabled by default and stored in AUD$
ldap_directory_sysauth= #YES: if a password file is configured then it will be checked before other strong authentications methods.
# O P T I M I Z E R
optimizer_capture_sql_plan_baselines=FALSE(def.), TRUE #db create and maintains a plan history for repeatable SQL, dynamic
optimizer_use_sql_baselines=FALSE(def.) #TRUE plan history is automatically captured and mantained for repeated SQL statements.
optimizer_use_private_statistics
optimizer_use_pending_statistics=FALSE(def.) #Dynamic. Alter session to test pending statistics.
optimizer_use_invisible_indexes=FALSE(def.) #dynamic. TRUE all invisible indexes are treated as visible
optimizer_features_enable #may force the optimizer to work as in 10g
#
control_management_pack_access=NONE, DIAGNOSTIC, DIAGNOSTIC+TUNING(def.)
java_jit_enabled=true #default is true. Java code will be compiled natively with no C compiler
control_management_pack_access=DIAGNOSTIC+TUNING(def.), NONE(disable ADDM)
memory_target
memory_max_target
diagnostic_dest= #ADR base (optional). Default diagnostic_dest=%ORACLE_BASE if %ORACLE_BASE defined else $ORACLE_HOME/log, many homes allowed
ddl_lock_timeout=0(def.) #dynamic. length of time for a ddl waiting a busy resource, max 1000000(11.5 dd)
db_securefile=ALWAYS, FORCE, PERMITTED(def.), NEVER, IGNORE #dynamic.
#ALWAYS: if tbs is enabled for ASSM Automatic Segment Space Management creates all lob as SecureFile otherwise creates BasicFiles.
#FORCE create all lob as SecureFiles regardless of the tablespace ASSM.
#PERMITTED SecureFiles creation is allowed.
#NEVER SecureFile creation is never allowed.
#IGNORE SecureFile creation is not allowed and ignores errors creating BasicFiles with SecureFile options
db_ultra_safe=OFF(1:OFF 2:TYPICAL 3:TYPICAL), DATA_ONLY(1:MEDIUM 2:FULL 3:TYPPICAL), DATA_AND_INDEX(1:FULL 2:FULL 3:TYPICAL)
1 db_block_checking=TYPICAL, OFF or FALSE, MEDIUM, FULL or TRUE
2 db_block_checksum=FALSE(def.), FULL(recomm.)
3 db_lost_write_protect=TYPICAL
statistic_level=[TYPICAL | ALL | BASIC] #BASIC will disable AWR providing SQL sources to Automatic SQL Tuning Advisor and ADDM
filesystemio_options=[ASYNCH | SETALL] #ASYNCH or SETALL allows I/O calibration
timed_statistics=true #TRUE allows I/O calibration

sqlnet.ora new param
encryption_wallet_location=(source=(method=file)(method_data=(directory=/yourdir)))

Database Replay
Useful for debugging, upgrading the OS, converting from a single instance to RAC
Captures user switching sessions, logins, all SQL(text, context and frequency), DML, DDL, session and system control calls(alter session..., alter system...)
NOT captures flashback SQL, direct path loads, distributed transactions, background activities, scheduled jobs

Steps
1) With SYSDBA or SYSOPER required, you should(but not required) restart the db in RESTRICTED mode before capture
2) Create a directory to store the capture files and ensure space is enough
3) capture and optionally define filters
4) This step is optional but recommended. You should recreate a test environment identical to what the capture was at the beginning and reset the system clock.
5) Move captured file to test environment
6) Preprocess captued workload, this only happens once and simply converts captured data into files that can be replayed
7) Remap connection strings and modify database links
8) Set up one or more clients, each client can control one or more workload sessions

Upgrading
1)run @/app/oracle/product/11.1.0/db_1/rdbms/admin/utlu111i.sql(Pre-Upgrade Information Tool) from 10g database
1.5) startup upgrade# from 11g
2)@?/rdbms/admin/catupgrd.sql
3)@?/rdbms/admin/utlu111s.sql(post upgrade status)
4)@?/rdbms/admin/catuppst.sql(post upgrade actions)
5)@?/rdbms/admin/utlrp.sql[no params | 0 | 1 | n]
#No params: parallel recompilation based on CPU_COUNT. 1: serial. n: parallel n
6)Enhance compatibility param
* Avoid plan regression *
Before upgrade capture plans for SQL Workload into an Sql Tuning Set(STS)
After upgrade manually load the old plans

ADR Automatic Diagnostic Repository
diagnostic_dest=ADR base (optional)
default diagnostic_dest=%ORACLE_BASE if %ORACLE_BASE defined else $ORACLE_HOME/log, many homes allowed
max 5 diagnostic data per hour per problem
ADR Command Interpreter
Many homes allowed concurrently, use SET HOMEPATH. Not all commands works with multiple homes, homepath is null by default, all homes are current by default
view XML alert,
show hm_run, create report hm_run , show report hm_run
* Views *
V$DIAG_INFO show all locations

ADRCI - ADR Command Interface
Used to view diagnostic data, view content of alert log file, package incidents and problem informations

Incident Packaging Service
<- EM Support Workbench(Support Workbench->Incident Package Configuration)(Collect diagnostic data, Generates incident reports and transmits), ADRCI
Critical Error=Problem=ORA-04031, composed of many Incidents=ORA-04031
Incident(Collecting, Ready, Tracking, Closed, Data Purged)
Retention: 1 year metadata(retention may be changed with EM), 1 month dumps. Incident is closed after twice retention elapses, is purged 30 days after open
1 Generate logical pack, 2 finalize, 3 gen. physical
Incident thresholds cannot be modified.
No edit and customizing when Quick Packaging
Oracle Support Workbench may be enabled to automatically upload files to Oracle Support by providing infos during the installation of
Oracle Configuration Manager
Flood-controlled incidents
are incidents reported multiple times. Alert log is updated and incident recorded in ADR but no dumps are generated.

SQL Performance Analyzer
1)First you capture SQL workload in a STS(SQL Tuning Set)
2)Move SQL workload to test environment
3)Execute the workload to capture baseline execution data
4)you make your changes
5)Execute the workload to capture execution data
6)Analyze differences in SQL performances
7)Tune regressed SQL statements
Identify plan that might have regressed after a database upgrade, hardware reconfiguration, schema changes and any change may affect SQL.
net SQL workload is identified. ?SQL are executed?
Cannot use SQL Performance Analizer to identify sql statements that are part of the SQL plan baseline
You can set a time limit within which all SQL statement in the workload must execute
DML and DDL are NOT taken in consideration
* Parameters *
TIME_LIMIT parameter for all SQL
LOCAL_TIME_LIMIT for single SQL
EXECUTION_TYPE[EXPLAIN PLAN | TEST EXECUTE(def.)]
#TEST EXECUTE: Each SQL Statement in the workload is executed one time and execution plans and statistics are collected.

SQL Repair Advisor
<- Support Workbench(Diagnostic Summary->Active Incidents link)
(often provides patch to fix)
sqlplan will change after patching. SQL Repair Advisor is not automatically launched after a SQL statement crash
* Export patches * DBMS_SQLDIAG.pack_stgtab_sqlpatch(staging_table), export, unpack_stgtab_sqlpatch(staging_table)
* Packages *
DBMS_SQLDIAG.create_diagnosis_task(sql_text, task_name)
DBMS_SQLDIAG.set_tuning_task_parameter(task_id, filter)
DBMS_SQLDIAG.execute_diagnosis_task(task_name)
DBMS_SQLDIAG.report_diagnosis_task(task_name, out_type)
DBMS_SQLDIAG.accept_sql_patch(task_name, task_owner)
* Views *
DBA_SQL_PATCHES(recommended patches), drop_sql_patch(procedure)

SQL Test Case Builder
<- EM Support Workbench
Theese informations are gathered for a problematic SQL statement: the text, involved table definitions, optimizer statistics. No dump files
DBMS_SQLDIAG.export_sql_testcase_dir_by_inc

Automatic Workload Repository
<- EM(Adaptive Metric Threshold link -> Baseline Metric Thresholds)
Requires AWR to have at least the same retention time.
A baseline is any set ot snapshot taken over a period of time.
Static baseline
Repeating static baseline
#Will be automatically created
Moving window baseline
#Will not be automatically created. SYSTEM_MOVING_WINDOW is system provided
Retention raise from 7 days(10g) to 8 days(11g) for default baseline SYSTEM_MOVING_WINDOW containing Adaptive Threshold
Thresholds types:
Significance Level
#if set to 0.99 alert on 1% change. Based on statistical relevance. Only Basic Metric support Significance level thresholds
Percentage of maximum
#calculated using the highest value captured by the baseline
Fixed values
When a baseline ends in the future a new baseline with the same name is created.
* Package *
DBMS_WORKLOAD.modify_baseline_window_size(wind_size)
#to change def. retention, if increase then increase AWR retention to match time
DBMS_WORKLOAD.create_baseline
#should be used for past time periods
DBMS_WORKLOAD.create_baseline_template
single: (start_time, end_time, bas_name, templ_name, EXPIRATION(O)=>null, dbid(O))
#EXPIRATION if not specified or null than the baseline template created will never expire
repeating:(day_of_week, hour_in_day, duration, expiration(O)=>null, start_time, end_time, baseline_name_prefix, template_name, dbid(O))
#should be used when part or all of the time period is on the future
DBMS_WORKLOAD.rename_baseline(old, new, dbid(O))
DBMS_WORKLOAD.modify_baseline_window_size
DBMS_WORKLOAD.drop_baseline_template(templ_name, dbid(O))
DBMS_WORKLOAD.select_baseline_metrics(baseline_name)(display metric thresholds)
DBMS_WORKLOAD_REPOSITORY...
#resize AWR retention period
* View *
DBA_HIST_BASELINE_TEMPLATE (template_type[single | repeated], repeat_interval[timing string as for DBMS_SCHEDULER])
DBA_HIST_BASELINE_DETAILS (shutdown[yes | no | null], pct_total_time[total snapshot time / total possible time], error_count[errors during snapshot])
DBA_HIST_BASELINE (baseline_type[static | moving | window | generated], creation_type, expiration[how long keep the baseline], template_name, last_computed,
moving_window_size[if baseline_type is null then AWR retention period else number of days of moving window])

Controlling Automated Maintenace Tasks
New ABP(Autotask Background Process)
- converts automatic tasks into Scheduler jobs
- determine the jobs that need to be created for each maintenance task
- stores task execution history in the SYSAUX tablespace
- does NOT execute maintenance tasks
A task may be enabled/disabled and changed of resource percentage in one or all maintenance windows. May change duration of maintenance windows.
Database Resource Manager is automatically enabled during maintenance window
By default AutoTask schedules: optimizer statistics collection, SQL Tuning Advisor and Automated Segment Advisor.
* MAINTENANCE_WINDOW_GROUP *
DEFAULT_MAINTENANCE_PLAN(belongs to a resource allocation)
10g: weeknight_window, weekend_window
11g: [10PM-02AM, 4h]: monday_window, tuesday_window, wednesday_window, thursday_window, friday_window. [06AM-02AM, 20h]saturday_window, sunday_window
* Managing Automatic Maintenance Tasks *
<- EM
10g: DBMS_SCHEDULER only enable or disable automatic maintenance tasks
11g: DBMS_AUTO_TASK_ADMIN more fine grained control
DBMS_AUTO_TASK_ADMIN.enable(client_name, operation, window_name)
DBMS_AUTO_TASK_ADMIN.disable(client_name, operation, window_name)
DBA_AUTOTASK_OPERATION lists only automatic task(clients) operations names
DBA_AUTOTASK_CLIENT
DBA_AUTOTASK_TASK #view ABP(Autotask Background Process) repository, stores execution history for automated maintenance tasks

I/O Calibration
<- EM(Database Control -> Performance -> I/O Calibration)
Requires 10 minutes to run, sysdba, filesystemio_options=[ASYNCH | SETALL], timed_statistics=true
recommended during low activity, no concurrent calibrations allowed
DBMS_RESOURCE_MANAGER.calibrate_io(num_disks=>1, max_latency=>10, max_iops, max_mbps, actual_latency)
V$IO_CALIBRATION_STATUS, DBA_RSC_IO_CALIBRATE, V$IOSTAT_FILE (all show calibration results)

Database Resource Manager
Automatic actions based on current session single call usage: switch of Resource Consumer Group(switch grant needed), session kill, statement kill
Database Resource Manager is automatically enabled during maintenance window
CREATE_RESOURCE_PLAN(switch_group[null | CANCEL_SQL | KILL_SESSION | a group], switch_time[UNLIMITED], switch_estimate[false | true],
switch_io_megabytes[null | mb], witch_io_reqs[null | io#], switch_for_call, switch_for_call[null | true])
CREATE_PLAN_DIRECTIVE(plan, group_or_subplan, mgmt_p1, switch_group[null | CANCEL_SQL | KILL_SESSION | a group], switch_io_reqs, switch_io_megabytes,
switch_for_call, switch_time)
MIXED_WORKLOAD_PLAN predefined resource plan
Level 1: 100% sys_group
Level 2: [85% interactive_group], [15% ORA$AUTOTASK_SUB_PLAN, ORA$DIAGNOSTIC, OTHER_GROUPS]
Level 3: batch_group

Oracle Scheduler
Minimum amount of metadata required for creation, recommended to create a big number of job
Create Lightweight jobs using: job array, named programs, schedules, DBMS_SCHEDULER.create_job
Lightweight jobs are created only from job templates(stored proc or Scheduler program), privileges are only inherited from parent job,
may discriminate DataGuard nodes
* Package *
DBMS_SCHEDULER.create_program(program_name, program_action, program_type[plsql_block | stored_procedure], enabled)
DBMS_SCHEDULER.create_job(job_name, program_name, repeat_interval(opt.), end_time(opt.), schedule_name(opt.), job_style[LIGHTWEIGHT], comments)
* Create a Job Array and submit using single transaction *
declare
testjob sys.job;
testjobarr sys.job_array;
begin
testjobarr:=sys.job_array();
testjobarr.extend(500);
testjob:=sys.job(...);
testjobarr(i):=testjob;
dbms_scheduler.create_jobs(testjobarr, 'transactional');
end;
* Remote External Jobs *
On source db: XML DB(default), run as SYS prvtrsch.plb, DBMS_SCHEDULER.set_agent_registration_pass(registration_password, expiration_date(O), max_uses(O))
On Dest host: install Scheduler agent(not default), configure schagent.conf, schagent.exe -registerdatabase hostdb 1521, schagent.exe -start
* Enable a remote job *
DBMS_SCHEDULER.create_credential(cred_name, username, pwd)
DBMS_SCHEDULER.create_job(...)
DBMS_SCHEDULER.set_attribute(job_name, 'credential_name', cred_name)
DBMS_SCHEDULER.set_attribute(job_name, 'destination', host:port), .enable(job_name)

Security Enhancements
sec* parameters
Pwd are case sensitive(default when NOT upgrading, reset the pwd otherwise), delay after pwd wrong up to 10 secs
orapwd file=... entries=... ignorecase=n(def.)
alter profile ... password_verify_function verify_function_11g
utlpwdmg.sql enforce pwd verification
OS authentication is checked before the password file
Authentication using OID can be configured by granting SYSOPER/SYSDBA enterprise role in OID
Administrators can be authenticated using local OS if they are in the OSDBA or OSPER group
* ACL Fine Grained Access to network system supplied packages*
An ACL is used to restrict access to certain hosts is stored in XML format. Connect comprise resove.
an ACL Access Control List is a users/privilege list, only one ACL per host
DBMS_NETWORK_ACL_ADMIN.create_acl(acl[name of XML DB priv file], description, principal, is_grant, privilege[connect | resolve])
#one privilege must be specified on creation
DBMS_NETWORK_ACL_ADMIN.add_privilege(acl[name of XML DB priv file], principal, is_grant, privilege[connect | resolve])
DBMS_NETWORK_ACL_ADMIN.assign_acl(acl[name of XML DB priv file], host[ex. *.fadalti.com], lower_port, upper_port)
DBMS_NETWORK_ACL_ADMIN.check_privilege(acl[name of XML DB priv file], username, privilege[connect | resolve])

Oracle Wallet
<- Oracle Wallet Manager(owm.exe)
see sqlnet.ora for location.
Encryption Wallet(manually open after db startup) and Auto-Open Wallet
Wallet required, configured and opened to encrypt tablespaces, [AES128, 3DES168, AES128, AES192, AES256], I/O overhead
Attempting to create a table in an ecrypted tablespace with the wallet closed raise an error.
You cannot change the key of an encrypted tablespace.
A normal tablespace cannot be converted to an encrypted tablespace.
Creation method 1: mkstore -wrl $ORACLE_BASE/admin/$ORACLE_SID/wallet -create
Creation method 2: alter system set encryption key identified by "password"
create tablespace pippo datafile 'c:\pippo.dbf' size 100m encryption using 'AES128' default storage(encrypt)

Automatic SQL Tuning Advisor
<- EM
statistic_level=TYPICAL, BASIC will disable AWR providing SQL sources. AWR retention less than 7dd disable ASTA.
STA may evolves SQL Plans from non-accepted to accepted
10g Automatic Tuning Optimizer(never auto tune) ->upgraded-> 11g Automatic SQL Tuning Advisor
job name SYS_AUTO_SQL_TUNING_TASK runs for 1 hour, runs only once during nightly maintenace window as part of MAINTENANCE_WINDOW_GROUP
Statistical analysis, SQL profiling, Access path analysis(add indexes), SQL structure analysis(Modifying SQL statements)
STA test and automaticall implement only SQL profiles creations with at least 66% of benefit,
choses statements from AWR Top SQL with poor performance in the past week or you manually run providing Sql Tuning Sets.
If run in Tuning Mode then the optimizer might run statistics and store in SQL Profiles
* Limitations * No recursive SQL, no ad hoc SQL, no parallel SQL, no long run profiled SQL, no insert/delete, no ddl
* Report *
Show if statisticd need to be collected, profile was recommended, index recommendation.
Show a benefit percentage of implementing recommendation for SQL profile and indexes
* Package *
DBMS_SQLTUNE.set_tuning_task_parameter(... see params below ...)
#for automatic tuning only, no manual
task_name=>'SYS_AUTO_SQL_TUNING_PROG'
parameter=>accept_sql_profile: When TRUE auto accept SQL profiles. Profiles are always generated
parameter=>replace_user_sql_profiles: allowed to replace user created SQL profiles
parameter=>max_sql_profiles_per_exec: maximum number of allowed SQL profiles changes accepted per tuning task
parameter=>max_auto_sql_profiles: maximum total number of allowed SQL profiles changes by db
parameter=>execution_days_to_expire: default 30, task history expiration
parameter=>time_limit: to change default duration of 1 hour in seconds
parameter=>test_execute: only use plan costs
DBMS_SQLTUNE.report_auto_tuning_task(type[text], level[typical], section[all]) return varchar2
DBMS_AUTO_TASK_ADMIN.enable(client_name=>'sql tuning advisor', operation=>'NULL', window_name=>['NULL'(all) | (a name)])
DBMS_AUTO_TASK_ADMIN.disable
#disable the Automatic Sql Tuning process
* View *
DBA_SQL_PROFILES[type], if type=AUTO then auto implemented SQL profile
DBA_ADVISOR_EXECUTIONS shows metadata information
DBA_ADVISOR_SQLSTATS shows a list of all SQL compilation and execution statistics
DBA_ADVISOR_SQLPLANS a list of all SQL execution plans

SQL Plan Management
Preserve SQL performance across major system changes
A SQL Plan may be:
non accepted #may be evolved to accepted
accepted #required for use, all manually loaded
enabled #required for use
fixed #A fixed plan has precedence over a non-fixed even if with higher cost, is FIXED if at least an enabled plan inside is FIXED=yes,
#the optimizer will not add new plans. If you accept a profile recommended by STA that profile will not be FIXED
SQL Tuning Advisor may evolve plans from non-accepted to accepted.
* Plan history * Contains both accepted and not-accepted plans for a repeatable SQL, best plan not accepted yet in SQL plan baseline
* SQL Plan Baseline * Contains plan history but only accepted
Automatic plan capture: see optimizer_capture_sql_plan_baselines parameter
Manual plan capture: performances are not verified
* Package *
DBMS_SQLTUNE.create_sqlset(sqlset_name, description) creates an empty SQL set
select value(p) from table(DBMS_SQLTUNE.select_workload_repository('peak baseline', null, null, 'elapsed time', null, null, null, 20)) p
DBMS_SQLSET.load_sqlset(sqlset_name, populate_cursor )
DBMS_SPM.load_plans_from_sqlset(sqlset_name, fixed=>no, enabled=>yes)
#plans are loaded into the SQL plan baseline as non-fixed accepted and enabled plans. May be used
DBMS_SPM.load_plans_from_cursor_cache(sql_id, plan_hash_value[null ], sql_text, fixed[no | yes ], enabled[yes],
attribute_name[sql_text | parsing_schema_name | module | action], attribute_value) return integer
DBMS_SPM.evolve_sql_plan_baseline(sql_handle, plan_list, plan_name, time_limit[DBMS_SPM.auto_limit | DBMS_SPM.no_limit],
verify[YES], commit[YES]) return CLOB
#show all non-accepted plans with status changed to accepted.
#VERIFY=YES the database executes the unaccepted plans and compare performances againts the SQL plan baseline
#VERIFY=NO all unaccepted plans became accepted
DBMS_SPM.alter_sql_plan_baseline(sql_handle, plan_name, attribute_name[accepted_status | enabled], attribute_value=>'yes')
DBMS_XPLAIN.display_sql_plan_baseline
* Views *
DBA_SQL_PLAN_BASELINES(sql_handle, sql_text, plan_name, origin[manual-load | manual-sqltune | auto-capture | auto-sqltune], enabled, accepted, fixed, autopurge)

SMB SQL Management Base
By default uses Automatic Segment Space Management ASSM
contains plan history information, the statement log, SQL profiles and plan baselines. Is part of data dictionary and stored in sysaux tbs.
sysaux tbs must be online to use SQL Plan Management features that access the SMB
DBMS_SPM.configure([SPACE_BUDGET_PERCENT | PLAN_RETENTION_WEEKS], value)
#SPACE_BUDGET_PERCENT: if the size of SMB exceed 10% of sysaux tablespace a weekly alert is generated
#PLAN_RETENTION_WEEKS: plan not used for more than 1 year are purged
* Views *
DBA_SQL_MANAGEMENT_CONFIG(SPACE_BUDGET_PERCENT, PLAN_RETENTION_WEEKS)

SQL Access Advisor
May be interrupted and resumed. By default is not scheduled as a maintenance task by the AutoTask.
Partitioning Recommendations for tables, indexes and materialized views, default mode is interval.
Partitioning Recommendations require at least 10000 rows on table, table must have some predicate or join with date or number , space, no bitmap indexes,
no interrupted SQL Access Advisor.
Advice on tuning Materialized Views, Materialized View Logs, indexes(B-tree, bitmap, function based)
* Packages *
DBMS_ADVISOR.add_sts_ref(task_name, sts_owner, sts_name)
#add an existing SQL tuning set
DBMS_ADVISOR.add_sqlwkld_ref(task_name, sts_name)
#add an existing SQL tuning set
DBMS_ADVISOR.delete_sts_ref(task_name, sts_owner, sts_name) link the SQL Access Advisor and the Workload
DBMS_ADVISOR.create_task(advisor_name=>'SQL Access Advisor', task_name)
DBMS_ADVISOR.set_task_parameter(task_name, param, value)[partition | segment][def_partition_tablespace, tbs][max_number_partitions,...]
[valid_table_list,...][mode,comprehensive][journaling,4][analysis_scope,all]
DBMS_ADVISOR.execute_task(task_name)
DBMS_ADVISOR.get_task_report(task_name, type[text], level[typical], section[all], owner_name, execution_name, object_id)
DBMS_ADVISOR.cancel_task(task_name) wont see any recommendation
DBMS_ADVISOR.interrupt_task(task_name) sometimes see recommendation, can't resume or restart
DBMS_ADVISOR.create_file(DBMS_ADVISOR.get_task_script(task_name), directory_name, file_name)
DBMS_ADVISOR.copy_sqlwkld_to_sts()
DBMS_ADVISOR.quick_tune(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_name, sql_text)
#task is automatically created

ADDM Automatic Database Diagnostic Monitor
control_management_pack_access=DIAGNOSTIC+TUNING and statistic_level=TYPICAL or ALL
Analyzes AWR data, present problems and recommendations. Runs automatically by default when a new AWR snapashot is taken. Enabled by default.
You can use directive to limit or filter the ADDM
* ADDM for RAC Real Application Cluster *
Database ADDM(analyze all RAC instances), Instance ADDM(analyze one RAC instances), Partial ADDM(analyze some RAC instances)
Analyze: [Global resources and global I/O], [High-Load SQL], [Contention across the instances], [Global cache interconnect traffic], [network latency issues],
[skews in instance report times]
* Packages *
DBMS_ADDM.analyze_db(task_name, begin_snapshot, end_snapshot)
#Creates an ADDM task global for all RAC instances
DBMS_ADDM.analyze_inst
#Creates an ADDM task for a single instance
DBMS_ADDM.analyze_partial
#Creates an ADDM task for some instances
DBMS_ADDM.delete
#delete an ADDM task
DBMS_ADDM.get_report(task_name)
#Gets a report of an ADDM task
DBMS_ADDM.delete_finding_directive;
DBMS_ADDM.insert_finding_directive(task=>null, directive=>'SGA Directive', finding=>'Undersized SGA', minactivesessions=>5, minpctimpact=>50)
#TASK : null means all subsequently created tasks
#FINDING: the finding in DBA_ADVISOR_FINDING_NAMES
DBMS_ADDM.delete_sql_directive; DBMS_ADDM.insert_sql_directive
#Limit action
DBMS_ADDM.delete_segment_directive;
#Limit action
DBMS_ADDM.insert_segment_directive
#Prevent ADDM from reporting a schema, segment, subsegment or object
DBMS_ADDM.delete_parameter_directive; DBMS_ADDM.insert_parameter_directive(param_name)
#Prevent ADDM from altering specified parameter
DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER (advisor_name, parameter, value)
#Specify the mode in wich to run ADDM [Database | Instance | Partial]
* Views *
DBA_ADVISOR_FINDINGS(finding_name) #USER_... available. Shows which findings occur most frequently in the db
DBA_ADDM_TASKS #USER_... available. Show all executed ADDM tasks
DBA_ADDM_INSTANCES #USER_... available. Instance level info for completed ADDM tasks
DBA_ADD_FINDINGS #USER_... available. Extends DBA_ADVISOR_FINDINGS
DBA_ADVISOR_FINDING_NAMES #USER_... available. Lists all registered finding names
DBA_ADVISOR_RECOMMENDATIONS(filtered[Y | N])
DBA_ADVISOR_ACTIONS(filtered[Y | N])

Automatic Memory Management
sga_target=0 #static; pga_target=0 #static; memory_target=900M #dynamic; memory_max_target=2000M;
show parameter target; #archive_lag_target=0, db_flashback_retention_target=1440, fast_start_io_target=0, fast_start_mttr_target=0, memory_max_target=900M,
memory_target=900M, pga_aggregate_target=0; sga_target=0
sga_target pga_aggregate_target memory_target memory_max_target result
0 0 900M 999M AMM enabled, defaults 60% to SGA and 40% to PGA
50M 100M 900M 999M AAM enabled, SGA minimum 50M, PGA minimum 100M
50M 0 900M 999M AAM enabled, SGA minimum 50M, PGA minimum is memory_target-50M
0 100M 900M 999M AAM enabled, SGA minimum is memory_target-100M, PGA minimum 100M
900M 0 or null AAM enabled, max_memory_target is memory_target. (SGA is not autotuned, PGA is)
50M Automatically tuned only SGA subcomponents. PGA is autotuned. max_memory_target=0
0 or null 999M AAM disabled. memory_target=0 default value
* Views *
V$MEMORY_DYNAMIC_COMPONENTS(component, current_size, user_specified_size)
V$MEMORY_CURRENT_RESIZE_OPS #current resizes in progress

Optimizer Statistics Collection
Automatically during nightly manintenance window, precedence for most needing objects. Can set preferences at table, schema, database and global level.
Both global(incrementally but no with histograms) and local statistics for partitioned tables. Statistics job is atomic at schema level
Statistics are collected for user-defined function and function-based indexes
A pending statistic is not yet available
* Options, Statistics preferences *
publish[true | false]: def. true. Store statistics in data dictionary, current statistics
stale_percent: def. 10%. Threshold level for an object to be stale
* Granularity * auto, global, global and partition, all, partition, subpartition
* Extended Statistics * multi column or expression. Collects number of distinct values, density, number of nulls, frequency histograms

Capture statistics, Test and Publish
1) alter session set optimizer_use_pending_statistics=false;
2) set PUBLISH option to false using SET_TABLE_PREFS procedure
#subsequent statistics will be gathered as pending
3) gather the statistics for table;
4) alter session set optimizer_use_pending_statistics=true;
5) test the statistics
6) execute the PUBLISH_PENDING_STATS procedure

Capture global statistics for a partitioned table INCREMENTALLY, only on last partition
Incremental works if granularity=global and table marked both incremental and publish, estimate_percent=>auto_sample_size[always recommended]
1) set INCREMENTAL value to true use SET_TABLE_PREFS procedure
2) DBMS_STATS.gather_table_stats(ownname=>'ALDO', tabname=>'persone', granularity=>'GLOBAL')
* Packages *
DBMS_STATS.gather_database_stats_job_proc
DBMS_STATS.set_prefs
#set preferences at various levels
DBMS_STATS.set_table_prefs(schema, table, pref_name, pref_value)
#PUBLISH =[true(def.) | false] FALSE: statistics taken will be pending
#INCREMENTAL=[true | false(def.)] TRUE: statistics will no be collected for partition that have not been changed
DBMS_STATS.set_schema_prefs
DBMS_STATS.resume_gather_stats
DBMS_STATS.get_prefs(['estimate_percents' | 'stale_percent' | 'publish'], schema, table)
DBMS_STATS.publish_pending_stats(schema[null(def.)], table[null(def.)])
#pending stats became public
DBMS_STATS.delete_pending_stats(schema[null(def.)], table[null(def.)])
DBMS_STATS.export_pending_stats(schema[null(def.)], table[null(def.)])
DBMS_STATS.create_extended_stats(ownname, tabname, extension=>'(name, surname)')
# select DBMS_STATS.create_extended_stats(null, 'persone', extension=>'(name, surname)') from dual;
#collect extended statistics. A column group is created. A virtual hidden column is created with a system-defined name
DBMS_STATS.drop_extended_stats(ownname, tabname, extension=>'name, surname')
DBMS_STATS.show_extended_stats(ownname, tabname, extension=>'name, surname') return varchar2
#return the name of the statistics entrys
DBMS_STATS.gather_table_stats(ownname=>null, tabname=>'pippe', method_opt=>'for all columns size skewonly for columns(lower(a),b) skewonly')
#coll. ext. stats
* Views *
DBA_TAB_STATS, DBA_IND_STATS stored published statistics
DBA_TAB_STAT_PREFS(owner, table_name, preference_name, preference_value) #shows current settings for statistics preferences
DBA_TAB_PENDING_STATS
DBA_STAT_EXTENSIONS(extension_name, extension)

Result Cache
In SGA(shared pool). Affected by memory_target, sga_target, shared_pool_size. Recently-used algorithm to age out results.
Can cache flashback SQL, can cache query fragments. Used both for SQL queries and deterministic functions.
/*Hint*/: result_cache and no_result_cache can use in a inline view
Result Cache Memory pool
SQL Query Result Cache
Pl/SQL Function Result Cache
client side
* Restrictions *
can't use SQL Query Result Cache on: Temporary tables, Dictionary tables, non-deterministic Pl/Sql functions(use deterministic keyword),
[currval,nextval,sysdate,sys_timestamp,current_date,current_timestamp,local_timestamp,userenv,sys_context,sys_quid], undergoing modification data, subqueries,
pipelined functions, functions can't have OUT or IN parameters, no anonymous blocks, no in package with invoker's right, no IN params as LOB, REF CURSOR,
RECORD, COLLECTION, OBJECT
create or replace function pippo(id number) return pp_record RESULT_CACHE RELIES_ON (persone) is ...
#RELIES_ON means that cached results will became invalid when the structure of any database object on which they depend is changed
* Packages *
DBMS_RESULT_CACHE.memory_report
#Reports cache enabled, block size, max cache size, max result size, memory [total | fixed | dynamic]
DBMS_RESULT_CACHE.status
#enabled/disabled, in RAC shows if synchronizing
DBMS_RESULT_CACHE.flush return boolean
#return true on successful removal, disable/close the cache DBMS_RESULT_CACHE.bypass(true) before flushing
DBMS_RESULT_CACHE.bypass([true | false])
#bypass the cache
* Views *
V$RESULT_CACHE_STATISTICS #list cache settings and memory usage stats
V$RESULT_CACHE_OBJECTS(type, status[new | published | bypass | expired | invalid], name) #list cached objects and attributes
V$RESULT_CACHE_DEPENDENCY
V$RESULT_CACHE_MEMORY #show all memory blocks and statistics

Client Result Cache
OCI based, cache only top-level SQL statements no query fragments, excellent on lookup tables. Independent from server result cache
client sqlnet.ora init params override the db server init params
* Restrictions *
no views, no remote objects, no complex types, no flashback, no SQL with pl/sql, no VPD policies on the tables
* Views *
CLIENT_RESULT_CACHE_STAT$ #show client settings and usage stats

Adaptive Cursor Sharing
Automatic feature can't be turned off, no init params. Works the same for bind vars and literals.
10g: Bind variable not good for plan generation, first time will determine the plan forever(bind peeking)
11g: Generate new child cursors
Bind sensitive cursor: values are so different that a new plane is needed. New plans are added to plan history but not used until db has proved a better
performance. See optimizer_capture_sql_plan_baselines. Once the plane is changed the cursor became bind-aware, more plans will be used depending on params
* Views *
V$SQL(is_bind_sensitive, is_bind_aware)
V$SQL_CS_HISTOGRAM #shows distribution of exceution count across execution history histogram
V$SQL_CS_SELECTIVITY #shows selectivity ranges stored in cursor for predicates with bind variables
V$SQL_CS_STATISTICS #stats for different bind sets, buffer gets and CPU time

Flashback Data Archive FDA - Oracle Total Recall
Requires Automatic Undo Management. Archivelog is not required. Data archive tablespaces must use ASSM
Store data changes compressed for any length of time you want in one or more tbs in an internal history table for each table tracked.
Only for some tables you choice. You can't modify data in Flashback, only purge.
Table/s must be enabled for tracking(flashback archiving).
Can set different retention for different tables, when in different archives, assigning different flashback data archives.
Retention at table level, must be specified for data archive. An internal historical table is created with few more columns and range partitioned.
FBDA process, sleep time automatically adjusted. Can use flashback SQL(as of).
Old data is automatically purged. May create default archive(not required)
* Differences from Flashback Database FD* FD may take db back in time, while FDA is read only. FDA is online FD is offline.
FD at db level while FDA at table level.
* Limitations *
no [drop | rename | modify] column, yes add column. no partition operations. no LONG to LOB conversion. no [drop | truncate] table
SQL>grant flashback archive ADMINISTER to user;
#ADMINISTER allows [create flashback archive, alter flashback archive, drop flashback archive]
SQL>grant flashback archive on t1 to user;
#user also need select privs, execute on DBMS_FLASHBACK
SQL>create flashback data archive f1 tablespace tbs1 quota 2000m RETENTION 4 year;
#RETENTION must be specified for data archive
alter flashback archive f1 purge all; alter flashback archive f1 purge before timestamp(systimestamp - interval '2' day);
alter flashback archive f1 purge before scn 1233
alter table t1 noflashback archive f1;
#all flashback data is removed
alter flashback archive fla1 modify retention 1 year
select * from t1 versions between timestamp to_timestamp(...) and maxvalue
* Views *
DBA_FLASHBACK_ARCHIVE(flashback_archive_name, retention_in_days, status), DBA_FLASHBACK_ARCHIVE_TS(quota_in_mb), DBA_FLASHBACK_ARCHIVE_TABLES

Flashback Transaction Blackout
database in archivelog, undo and redo data are needed. supplemental logging and primary key supplemental logging at db level must be enabled.
Transaction dependencies:
write-after-write: dependent transaction modifies data modified by parent transaction
primary key constraint: dependent transaction reinserts the primary key deleted by parent transaction
alter database add supplemental log data;
alter database add supplemental log data (primary key) columns;
grant execute on dbms_flashback to hr;
grant select any transaction to hr;
grant insert, update, delete on pippo to hr;
#grant DML on specific involved tables
* Packages *
DBMS_FLASHBACK.transaction_backout(numberofxids, xids(array), options[see below...], scnhint(SCN at the start of transaction) or timehint)
#no commit inside!
nocascade: (def.) no depent transactions expected
cascade: dependent transactions are backed out before the parent
nocascade_force: dependent transactions are ignored
noconflict_only: only those rows with no conflict in parent transactions
* Views *
DBA_FLASHBACK_TRANSACTION_STATE #backed out transactions
DBA_FLASHBACK_TRANSACTION_REPORT

Oracle SecureFiles
10g lob->11g BasicFiles
transparent encryption. variable chunk size max 64M stored next to one another. redo generated only for changed data.
new client/server network for faster data transfer.
Every option may be changed later using . long api can't be used to configure SecureFile setting.
COMPATIBLE min 11.0. Error raisen if you use SecureFile capabilities in a BasicFile.
1) DEDUPLICATION detect and avoid duplicated data, Advanced Compression is required, only one copy is saved, deduplication is disabled by default
2) COMPRESSION [HIGH | MEDIUM(def.)], compression is disabled by default
3) ENCRYPTION must use Advanced Security Option [AES128 | 3DES168 | AES128 | AES192 | AES256], encryption is disabled by default
Older storage cluses(chunk, pctversion, freelist) no necessaries with SecureFiles, instead use this:
MAXSIZE: maximum LOB segment size
RETENTION: version control policy
MAX: after MAXSIZE old version is used
MIN: old version is retained for at least the time specified
AUTO: default.
NONE: old version is use ad much as possible
create table pippo(l clob) LOB(l)STORE AS [securefile | lob] (COMPRESS [HIGH | MEDIUM] deduplicate lob [CACHE | NOCACHE | CACHE READS] nologging);
#STORE AS is optional, default is LOB traditional.
#CACHE will place lob pages in the buffer cache for speedier access, default is NOCACHE, CACHE READS cache only during reads not for writes.
#NOLOGGING will not generate any redo
#COMPRESS [HIGH | MEDIUM(def.)]
#(NO) KEEP DUPLICATES #specify whether the database will store duplicates for the LOB column
create table pippo (doc clob)lob(doc) store as securefile(ENCRYPT);
create table pippo (doc clob encrypt using 'AES256')lob(doc) store as securefile;

Migrating to SecureFiles
* Partition Exchange *
Ensure you have enough space for largest partition, long maintenance window, segment offline
* Online Redefinition (recommended) *
No segment offline, migration in parallel, index must be rebuild, double space required.
Materialized view must be refreshed if the source table is redefined.
create table tab1(id number, c lob) lob(c) store as lob;
create table tab1(id number, c lob) lob(c) store as securefile;
DBMS_REDEFINITION.start_redef_table('scott', 'tab1', 'tab2', 'id id c c');
DBMS_REDEFINITION.copy_table_dependents('scott', 'tab1', 'tab2', 1, true, true,true,true,false, error_count);
DBMS_REDEFINITION.finish_redef_table('scott', 'tab1', 'tab2');
* Packages *
DBMS_LOB.getoptions
#return lob settings
DBMS_LOB.setoptions
DBMS_LOB.get_deduplicated_regions
DBMS_SPACE.space_usage
#determine disk space used by all lob in segment, only for ASSM segments
* Views *
DBA_SPACE, DBA_LOB

Locking Enhancements
By default a DDL will fail if can't get an immediate DML lock on the table.
lock table pippo in exclusive mode [nowait | wait(def.) (optional)]
#if mode omitted will wait undefinitely
The following statements will not require a lock:
create index online;
create materialized view log;
alter table enable constraint novalidate;
Minimal invalidation - Fine Grained Dependency Management
Only logical affected objects are invalidated such as views and synonyms during online redefinition, triggers have not been enhanced they will be invalidated.

Parameter file creation from Memory
create [spfile | pfile] from memory;
#will have values for all 150 init parameters even if not precedently set

Hot Patching
Supported on RAC
opatch enable, disable and install patches.
Currently only for Linux, Solaris. Consumes extra memory depending on the number of currently running Oracle processes.
At least one OS page of memory(4-8kb) for Oracle process is required.
opatch query -is_online_patch
#determine if patch is hot

Invisible Indexes
Invisble to the optimizer, discarded from plans. Can change from in/visible at any time.
create index inv1 on pippo(nome) invisible;
alter index i1 in/visible;
* Views *
DBA_INDEXES(visibility)

Shrinking Temporary Tablespaces
May shrink online temporary tablespaces and single tempfiles. Shrink to a minimum of 1MB(def.)
alter tablespace temp shrink space [keep 100m];
#Not equally distributed along tempfiles
alter tablespace temp shrink tempfile '/pippo.dbf' [keep 100m]
* Views *
DBA_TEMP_FREE_SPACE(tablespace_name, free_space) #FREE_SACE in bytes. Space unallocated and allocated space available to be reused

Tablespace Option for Temporary tables
May manually specify a temporary tbs, indexes are created in the same tablespace

PL/SQL and Java Automatic Native Compilation
see parameter plsql_code_type and plsql_optimize_level
alter procedure pioppo compile pl_sql_code_type=native;
* Recompiling a Database for Pl/Sql Native Compilation *
1) shutdown immediate; 2) plsql_code_type=native, plsql_optimize_level=3 3) startup upgrade 4) @?/rdbms/admin/dbsupgnv.sql 5) restart and utlrp.sql
* Views *
DBA_PLSQL_OBJECT_SETTINGS(plsql_code_type)

OLTP Table Compression
Writes not degraded reads improved because data is directly read as compressed. Disk space and memory are minimized, cpu is increased.
May compress data during dml, not only when bulk loading(10g) or when creating CTAS(10g). Holes made by deletes are eliminated
Compression may be enabled for tables, partitions and tablespaces.
A block is compressed when reaches PCTFREE.
create table pippo(...) compress; #normal compress
create table pippo(...) compress for direct_load_operations #normal compress for a data warehouse workloads table not for OLTP
create table pippo(...) compress for all operations #compress for dml operations
alter table pippo compress for all operations #An existing table may be altered to compress but existing data remain uncompressed.
* Views *
DBA_TABLES(compression, compress_for)

Direct NFS Client
Simplify manageability across multiple platforms, performance are increased but kernel NFS is not completely bypassed since its used for network communication
Avoid kernel NFS layer. Load balancing. You may control I/O path. OS must perform the mount. NFS version 3 protocol implemented in Oracle RDBMS kernel.
If mismatch between oranfstab and OS mount points the NFS is stopped. To remove an NFS path in use restart the database.
Direct NFS client will disabled if delete oranfstab or modify its setting or replacing ODM NFS with the stub libodn11.so
If the database can't open the NFS using Direct NFS it will use OS kernel
* Configuring *
1) Specify mount point in /etc/mtab first default location and be sure the OS perform the mount
2) (optional) edit oranfstab for Oracle specfic options such as additional paths to a mount point.
Mount point search order:
(optional) $ORACLE_HOME/dbs/oranfstab
(optional) /etc/oranfstab
(when no oranfstab) /etc/mtab
4) Enable Direct NFS replacing standard ODM library libnfsodm10.so with ODM NFS library $ORACLE_HOME/lib/libodm11.so
oranfstab format:
server: TestServer1 #nfs server name
path: 130.33.34.11 #up to 4 network paths if the first fail, load balancing
export: /vol/oradata1 #exported path from NFS server
mount: /mnt/oradata1 #local mount point
* Views *
V$DNFS_STATS #show performance stats for Direct NFS
V$DNFS_SERVERS #show servers accessed by Direct NFS
V$DNFSFILES #files currently using Direct NFS
V$DNFS_CHANNEL #open network paths

RAC 10g administration - Basic

2009-05-03T00:14:00.000-07:00

RAC benefit and characteristics
- does not protect from human errrors
- increased availabilty from node/instance failure
- speed up parallel DSS queries
- no speed up parallel OLTP processes
- no availability increase on data failures
- no availability increase on network failures
- no availability increase on release upgrades
- no scalability increased for applications workloads in all cases

RAC tuning - After migration to RAC test:
- Interconnect latency
- Instance recovery time
- Application strongly relying on table truncates, full scan tables, sequences and non-sequences key generation,
global context variables

RAC specific background processes for the database instance
Cluster Synchronization Service (CSS)
ocssd daemon, manages cluster configuration

Cluster Ready Services (CRS)
manages resources(listeners, VIPs, Global Service Daemon GSD, Oracle Notification Service ONS)
crsd daemon backup the OCR every for hours, configuration is stored in OCR

Event Manager (EVM)
evmd daemon, publish events

LMSn coordinate block updates
LMON global enqueue for shared locks
LMDn manages requests for global enqueues
LCK0 handle resources not requiring Cache Fusion
DIAG collect diagnostic info

GSD 9i is not compatible with 10g

FAN Fast Application Notification
- Must connect using service

Logged to:
&ORA_CRS_HOME/racg/dump
$ORA_CRS_HOHE/log//racg

VERSION=
service=
[database= [instance=]]
[host=]
status= reason= [card=]
timestamp=

event_type Description
SERVICE Primary application service event
SRV_PRECONNECT Preconnect application service event (TAF)
SERVICEMEMBER Application service on a specific instance event
DATABASE Database event
INSTANCE Instance event
ASM ASM instance event
NODE Cluster node event

#FAN events can control the workload per instance for each service

Oracle Notification Service ONS
- Transmits FAN events
- For every FAN event status change, all executables in $ORA_CRS_HOME/racg/usrco are launched (callout scripts)

The ONS process is $ORA_CRS_HOME/opmn/bin/ons
Arguments:
-d: Run in daemon mode
-a : can be [ping, shutdown, reload, or debug]

[$ORA_CRS_HOME/opmn/conf/ons.config]
localport=6lOO
remoteport=6200
loglevel=3
useocr=on

onsctl start/stop/ping/reconfig/debug/detailed

FCF Fast Connection Failover
- A JDBC application configured to use FCF automatically subscribes to FAN events
- A JDBC application must use service names to connect
- A JDBC application must use implicit connection cache
- $ORACLE_HOME/opmn/lib/ons.jar must be in classpath
- -Doracle.ons.oraclehome -
or
System.setProperty ("oracle.ons.oraclehome", "/u01/app/oracle/product/10.2.0/db_l");

OracleDataSource ods = new OracleDataSource();
ods.setUser("USERl");
ods.setPassword("USERl");
ods.setConnectionCachingEnabled(true);
ods.setFastConnectionFailoverEnabled(true);
ods.setConnectionCacheName("MyCache");
ods.setConnectionCacheProperties(cp);
ods.setURL("jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=londonl-vip)(PORT=152l)(ADDRESS=(PROTOCOL=TCP)(HOST=london2-vip)(PORT=152l)(CONNECT_DATA=(SERVICE_NAME=SERVICE1)))")

Check for main Clusterware services up

#check Event Manager up
ps -ef | grep evmd
#check Cluster Synchronization Services up
ps -ef | grep ocssd
#check Cluster Ready Services up
ps -ef | grep crsd
#check Oracle Notification Service
ps -ef | grep ons

[/etc/inittab]
...

crs_stat
#Tested, as root
#Lists the status of an application profile and resources
#crs_stat [resource_name [...]] [-v] [-l] [-q] [-c cluster_node]
$ORA_CRS_HOME/bin/crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.e2.gsd application ONLINE ONLINE e2
ora.e2.ons application ONLINE ONLINE e2
ora.e2.vip application ONLINE ONLINE e2

VIP Normal
Name Type Target State Host
------------------------------------------------------------
ora.e2.vip application ONLINE ONLINE e2
ora.e2.vip application ONLINE ONLINE e3
VIP Node 2 is down
Name Type Target State Host
------------------------------------------------------------
ora.e2.vip application ONLINE ONLINE e2
ora.e2.vip application ONLINE ONLINE e2

crs_stat -p ...
AUTO_START = #2 CRS will not start after system boot

crs_stat
NAME=ora.RAC.RACl.inst
TYPE=application
TARGET=ONLINE
STATE=ONLINE on londonl

NAME=ora.RAC.SERVICEl.RACl.srv
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE

#use -v for verbose resource use
#use -p for a lot of details
#use -ls to view resources and relative owners

Voting disk
On Shared storage, Used by CSS, contains nodes that are currently available within the cluster
If Voting disks are lost and no backup is available then Oracle Clusterware must be reinstalled
3 way multiplexing is ideal

#backup a voting disk online
dd if= of=

crsctl
#Tested, as oracle
$ORA_CRS_HOME/bin/crsctl check crs
Cluster Synchronization Services appears healthy
Cluster Ready Services appears healthy
Event Manager appears healthy

#add online a new voting disk(10.2), -force if Oracle Clusterware is not started
crsctl add css votedisk 'new votedisk path' -force

crsctl start/stop/enable/disable crs

#set/unset parameters on OCR
crsctl set/unset

You can list the currently configured voting disks:
crsctl query css votedisk
0. 0 /u02/oradata/RAC/CSSFilel
1. 1 /u03/oradata/RAC/CSSFile2
2. 2 /u04/oradata/RAC/CSSFile3

Dynamically add and remove voting disks to an existing Oracle Clusterware installation:
crsctl add/delete css votedisk -force

CRS log and debug
#as root, enable extra debug for the running CRS daemons as well as those running in future
#enable to inspect system reboots
crsctl debug log crs

#Collect log and traces to upload to Oracle Support
diagcollection.pl

OCR - Oracle Cluster Registry

[/etc/oracle/ocr.loc](10g) or [/etc/oracle/srvConfig.loc](9i, still exists in 10g for compatibility)
ocrconfig_loc=/dev/raw/rawl
ocrmirrorconfig_loc=/dev/raw/raw2
local_only=FALSE

OCRCONFIG - Command-line tool for managing Oracle Cluster Registry
#recover OCR logically, must be done on all nodes
ocrconfig -import exp.dmp
#export OCR content logically
ocrconfig -export
#recover OCR from OCR backup
ocrconfig -restore bck.ocr
#show backup status
#crsd daemon backup the OCR every for hours, the most recent backup file is backup00.ocr
ocrconfig -showbackup
londonl 2005/08/04 11:15:29 /uOl/app/oracle/product/lO.2.0/crs/cdata/crs
londonl 2005/08/03 22:24:32 /uOl/app/oracle/product/10.2.0/crs/cdata/crs
#change OCR autobackuo location
ocrconfig -backuploc
#must be run on each affected node
ocrconfig -repair ocr
ocrconfig -repair ocrmirror
#force Oracle Clusterware to restart on a node, may lose recent OCR updates
ocrconfig -overwrite

CVU - Cluster verification utility to get status of CRS resources
dd : use it safely to backup voting disks when nodes are added/removed

#verify restore
cluvfy comp ocr -n all

ocrcheck
#OCR integrity check, validate the accessibility of the device and its block integrity
log to current dir or to $OCR_HOME/log//client

ocrdump
#dump the OCR content to a text file, if succeds then integrity of backups is verified
OCRDUMP - Identify the interconnect being used
$ORA CRS HOME/bin/ocrdump.bin -stdout -keyname SYSTEM.css.misscount -xml

Pre install, prerequisite
(./run)cluvfy : run from install media or CRS_HOME, verify prerequisites on all nodes

Post installation
- Backup root.sh
- Set up other user accounts
- Verify Enterprise Manager / Cluster Registry by running srvctl config database -d db_name

SRVCTL
Stores infos in OCR, manages:
Database, Instance, Service, Node applications, ASM, Listener

srvctl config database -d : Verify Enterprise Manager / Cluster Registry
set SRVM_TRACE=TRUE environment var to create Java based tool trace/debug file for srvctl
#-v to check services
srvctl status database -d RAC -v SERVICE1
srvctl start database -d [-o mount]
srvctl stop database -d [-o stop_options]
#moves parameter file
srvctl modify database -d name -p /u03/oradata/RAC/spfileRAC.ora
srvctl remove database -d TEST
#Verify the OCR configuration
srvctl config database - TEST

srvctl start instance -d RACDB -i "RAC3,RAC4"
srvctl stop instance -d -i "orcl3,orcl4" -o immediate
srvctl add instance -d RACDB -i RAC3 -n londonS
#move the instance to node london4
srvctl modify instance -d RAC -i RAC3 -n london4
#set a dependency of instance RAC3 to +ASM3
srvctl modify instance -d RAC -i RAC3 -s +ASM3
#removes an ASM dependency
srvctl modify instance -d RAC -i RAC3 -r

#stop all applications on node
srvctl stop nodeapps -n londonl
#-a display the VIP configuration
srvctl config nodeapps -n londonl -a
srvctl add nodeapps -n london3 -o $0RACLE_H0ME -A london3-vip/255.255.0.0/eth0

Services
Changes are recorded in OCR only! Must use DBMS_SERVICE to update the dictionary

srvctl start service -d RAC -s "SERVICE1,SERVICE2"
srvctl status service -d RAC -s "SERVICE1,SERVICE2"
srvctl stop service -d RAC -s "SERVICE1,SERVICE2" -f
srvctl disable service -d RAC -s "SERVICE2" -i RAC4
srvctl remove service -d RAC -s "SERVICE2"

#relocate from RAC2 to RAC4
srvctl relocate service -d RAC -s "SERVICE2" -i RAC2 -t RAC4

#preferred RAC1,RAC2 and available RAC3,RAC4
#-P PRECONNECT automatically creates a ERP and ERP_PRECONNECT service to use as BACKUP in tns_names
#See TNSnames configuration
#the service is NOT started, must be started manually (dbca do it automatically)
srvctl add service -d ERP -s SERVICE2 -i "RAC1,RAC2" -a "RAC3,RAC4" -P PRECONNECT

#show configuration, -a shows TAF conf
srvctl config service -d RAC -a

#modify an existing service
srvctl modify service -d RACDB -s "SERVICE1" -i "RAC1,RAC2" -a "RAC3,RAC4"
srvctl stop service -d RACDB -s "SERVICE1"
srvctl start service -d RACDB -s "SERVICE1"

Views
GV$SERVICES
GV$ACTIVE_SERVICES
GV$SERVICEMETRIC
GV$SERVICEMETRIC_HISTORY
GV$SERVICE_WAIT_CLASS
GV$SERVICE_EVENT
GV$SERVICE_STATS
GV$SERV_MOD_ACT_STATS

SQL for RAC
select * from V$ACTIVE_INSTANCES;

Cache Fusion - GRD Global Resource Directory
GES(Global Enqueue Service)
GCS(Global Cache Service)

Data Guard & RAC
- Configuration files at primary location can be stored in any shared ASM diskgroup, on shared raw devices,
on any shared cluster file system. They simply have to be shared

VIP virtual IP
- Both application/RAC VIP fail over if related application fail and accept new connections
- Recommended RAC VIP sharing among database instances but not among different applications because...
- ...VIP fail over if the application fail over
- A failed over VIP application accepts new connection
- Each VIP requires an unused and resolvable IP address
- VIP address should be registered in DNS
- VIP address should be on the same subnet of the public network
- VIPs are used to prevent connection requests timeout during client connection attempts

Changing a VIP
1- Stop VIP dependent cluster components on one node
2- Make changes on DNS
3- Change VIP using SRVCTL
4- Restart VIP dependent components
5- Repeat above on remaining nodes

oifcfg
allocating and deallocating network interfaces, get values from OCR

To display a list of networks
oifcfg getif
eth1 192.168.1.0 global cluster_interconnect
eth0 192.168.0.0 global public

display a list of current subnets
oifcfg iflist
etho 147.43.1.0
ethl 192.168.1.0

To include a description of the subnet, specify the -p option:
oifcfg iflist -p
ethO 147.43.1.0 UNKNOWN
ethl 192.168.1.0 PRIVATE

In 10.2 public interfaces are UNKNOWN.
To include the subnet mask, append the -n option to the -p option:
oifcfg if list -p -n
etho 147.43.1.0 UNKNOWN 255.255.255.0
ethl 192.168.1.0 PRIVATE 255.255.255.0

Db parameters with SAME VALUE across all instances
active_instance_count
archive_lag_target
compatible
cluster_database RAC param
cluster_database_instance RAC param
#Define network interfaces that will be used for interconnect
#it is not a failover but a redistribution. If an address not work then stop all
#Overrides the OCR
cluster_interconnects RAC param = 192.168.0.10; 192.168.0.11; ...
control_files
db_block_size
db_domain
db_files
db_name
db_recovery_file_dest
db_recovery_file_dest_size
db_unique_name
dml_locks (when 0)
instance_type (rdbms or asm)
max_commit_propagation_delay RAC param
parallel_max_servers
remote_login_password_file
trace_enabled
#cannot be mixed AUTO and MANUAL in a RAC
undo_management

Db parameters with INSTANCE specific VALUE across all instances
instance_name
instance_number
thread
undo_tablespace #system param

Listener parameters
local_listener='(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.13) (PORT = 1521)))'
#allow pmon to register with local listener when not using 1521 port
remote_listener = '(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.9) (PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.2.10)(PORT = 1521)))'
#make the listener aware of the load of the listeners of other nodes

Important Rac Parameters
gc_files_to_locks #other than default disable Cache Fusion
recovery_parallelism #number of redo application server processes in instance or media recovery

Rac and Standby parameters
dg_broker_config_file1 #shared between primary and standby instances
dg_broker_config_file2 #different from dg_broker_config_file1, shared between primary and standby instances

Forum section
This browser does not support Applets.

Shared contents
datafiles, controlfiles, spfiles, redo log

Shared or local?
RAW_Dev File_Syst ASM NFS OCFS
- Datafiles : shared mandatory
- Control files : shared mandatory
- Redo log : shared mandatory
- SPfile : shared mandatory
- OCR and vote : shared mandatory Y Y N
- Archived log : shared not mandatory. N Y N Y
- Undo : local
- Flash Recovery : shared Y Y Y
- Data Guard broker conf.: shared(prim. & stdby) Y Y

Adding logfile thread groups for a new instance
#To support a new instance on your RAC
1) alter database add logfile thread 3 group 7;
1) alter database add logfile thread 3 group 8;
#makes the thread available for use by any instance
2) alter database enable thread 3;
# if you want to change an used thread
2) alter system set thread=3 scope=pfile sid='RAC01'
3) srvctl stop instance -d RACDB -i RAC01

Views and queries
select * from GV$CACHE_TRANSFER

An instance failed to start, what do we do?
1) Check the instance alert.log
2) Check the Oracle Clusterware software alert.log
3) Check the resource state using CRS_STAT

Install
See official Note 239998.1 for removing crs installation
See http://startoracle.com/2007/09/30/so-you-want-to-play-with-oracle-11gs-rac-heres-how/ to install 11g RAC on VMware
See http://www.oracle.com/technology/pub/articles/hunter_rac10gr2_iscsi.html to install on Linux with iSCSI disks
See http://www.oracle-base.com/articles/10g/OracleDB10gR2RACInstallationOnCentos4UsingVMware.php to install on VMware

#If using VMware remember to allow shared disks disabling locking
#Obviously this is not required if you use OCFS or ASM
disk.locking = "FALSE"
See OCFS Oracle Cluster Filesystem

Prerequisites check
#check node connectivity and Clusterware integrity
./runcluvfy.sh stage -pre dbinst -n all
./runcluvfy.sh stage -post hwos -n "linuxes,linuxes1" -verbose
WARNING:
Package cvuqdisk not installed.

rpm -Uvh clusterware/rpm/cvuqdisk-1.0.1-1.rpm

WARNING:
Unable to determine the sharedness of /dev/sdf on nodes:
linuxes1,linuxes1,linuxes1,linuxes1,linuxes1,linuxes1,linuxes,linuxes,linuxes,linuxes,linuxes,linuxes

Safely ignore this error

./runcluvfy.sh comp peer -n "linuxes,linuxes1" -verbose
./runcluvfy.sh comp nodecon -n "linuxes,linuxes1" -verbose
./runcluvfy.sh comp sys -n "linuxes,linuxes1" -p crs -verbose
./runcluvfy.sh comp admprv -n "linuxes,linuxes1" -verbose -o user_equiv
./runcluvfy.sh stage -pre crsinst -n "linuxes,linuxes1" -r 10gR2

Restart intallation - Remove from each node
su -c "$ORA_CRS_HOME/install/rootdelete.sh; $ORA_CRS_HOME/install/rootdeinstall.sh"
#oracle user
export DISPLAY=192.168.0.1:0.0
/app/crs/oui/bin/runInstaller -removeHome -noClusterEnabled ORACLE_HOME=/app/crs LOCAL_NODE=linuxes
rm -rf $ORA_CRS_HOME/*
#root
su -c "chown oracle:dba /dev/raw/*; chmod 660 /dev/raw/*; rm -rf /var/tmp/.oracle; rm -rf /tmp/.oracle"

#Format rawdevices using
dd if=/dev/zero of=/dev/raw/raw6 bs=1M count=250

#If related error message appears during installation, manually launch on related node
/app/crs/oui/bin/runInstaller -attachHome -noClusterEnabled ORACLE_HOME=/app/crs ORACLE_HOME_NAME=OraCrsHome CLUSTER_NODES=linuxes,linuxes1 CRS=true "INVENTORY_LOCATION=/app/oracle/oraInventory" LOCAL_NODE=linuxes

runcluvfy.sh stage -pre crsinst -n linuxes -verbose

Forum section
This browser does not support Applets.
/etc/hosts example
# Do not remove the following line, or various programs
# that require network functionality will fail,
127.0.0.1 localhost
147.43.1.101 londonl
147.43.1.102 london2
#VIP is usable only after VIPCA utility run,
#should be created on the public interface. Remember that VIPCA is a GUI tool
147.43.1.201 londonl-vip
147.43.1.202 london2-vip
192.168.1.1 londonl-priv
192.168.1.2 london2-priv

Kernel Parameters(/etc/sysctl.conf) Recommended Values
kernel.sem (semmsl) 250
kernel.sem (semmns) 32000
kernel.sem (semopm) 100
kernel.sem (semmni) 128
kernel.shmall 2097152
kernel.shmmax Half the size of physical memory
kernel.shmmni 4096
fs.file-max 65536
net.core.rmem_default 262144
net.core.rmem_max 262144
net.core.wmem_default 262144
net.core.wmem_max 262144
net.ipv4.ip_local_port_range 1024 to 65000

RAC restrictions
- dbms_alert, both publisher and subscriber must be on same instance, AQ is the workaround
- dbms_pipe, only works on the same instance, AQ is the workaround
- UTL_FILE, directories, external tables and BFILEs need to be on shared storage

Implementing the HA High Availability Framework
Use srvctl to start/stop applications

#Manually create a script that OCR will use to start/stop/status

#Create an application VIP.
#This command generates an application profile called haf demovip.cap in the $ORA_CRS_HOME/crs/ public directory.
$ORA_CRS_HOME/bin/crs_profile -create hafdemovip -t application -a $ORA_CRS_HOME/bin/usrvip -o oi=eth0,ov=147.43.1.200,on=255.255.0.0

#As the oracle user, register the VIP with Oracle Clusterware:
ORA_CRS_HOME/bin/crs_register hafdemovip

#As the root user, set the owner of the apphcation VIP to root:
$ORA_CRS_HOME/bin/crs_setperm hafdemovip -o root

#As the root user, grant the oracle user permission to run the script:
$ORA_CRS_HOME/bin/crs_setperm hafdemovip -u user:oracle:r-x

#As the oracle user, start the application VIP:
$ORA_CRS_HOME/bin/crs_start hafdemovip

2. Create an application profile.
$ORA_CRS_HOHE/bin/crs_profile -create hafdemo -t application -d "HAF Demo" -r hafdemovip -a /tmp/HAFDemoAction -0 ci=5,ra=60

3. Register the application profile with Oracle Clusterware.
$ORA_CRS_HOHE/bin/crs_register hafdemo

$ORA_CRS_HOME/bin/crs_start hafdemo

CRS commands
crs_profile
crs_register
crs_unregister
crs_getperm
crs_setperm
crs_start
crs_stop
crs_stat
crs_relocate

Server side callouts
Oracle instance up(/down?)
Service member down(/up?)
Shadow application service up(/down?)

Adding a new node
- Configure hardware and OS
- With NETCA reconfigure listeners and add the new one
- $ORA_CRS_HOME/oui/bin/addnode.sh from one of existing nodes to define the new one to all existing nodes
- $ASM_HOME/oui/bin/addnode.sh from one of existing nodes (if using ASM)
- $ORACLE_HOME/oui/bin/addnode.sh from one of existing nodes
- racgons -add_config to add ONS metadata to OCR from one of existing nodes

Removing a node from a cluster
- Remove node from clusterware
- Check that ONS configuration has been updated on other node
- Check that database and instances are terminated on node to remove
- Check that node has been removed from database and ASM repository
- Check that software has been removed from database and ASM homes on node to remove

RAC contentions
- enq:HW - contention and gc current grant wait events
Use larger uniform extent size for objects

- enq: TX - index contention
Re-create the index as a global hash partitioned index.
Increase the sequence cache size if retaining the sequence.
Re-create the table using a natural key instead of a surrogate key.

11g New Feature : Invisible Index

2009-03-23T05:14:00.000-07:00

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

Using invisible indexes, you can do the following:

1.Test the removal of an index before dropping it.

2.Use temporary index structures for certain operations or modules of an application without affecting the overall application.

Unlike unusable indexes, an invisible index is maintained during DML statements.

To create an invisible index, use the SQL statement CREATE INDEX with the INVISIBLE clause. The following statement creates an invisible index named emp_ename for the ename column of the emp table:

CREATE INDEX emp_ename ON emp(ename)
TABLESPACE users
STORAGE (INITIAL 20K
NEXT 20k
PCTINCREASE 75)
INVISIBLE;

To make a visible index invisible, issue this statement:

ALTER INDEX index_name INVISIBLE;

To make an invisible index visible, issue this statement:

ALTER INDEX index_name VISIBLE;

To find out whether an index is visible or invisible, query the dictionary views USER_INDEXES, ALL_INDEXES, or DBA_INDEXES. Column VISIBILITY is newly added to these views.

Data Guard Redo Apply and Media Recovery Best Practices 10gR1

2009-03-21T01:08:00.000-07:00

Data Guard Redo Apply and Media Recovery Best Practices
The best practices outlined in this paper have been derived after extensive media recovery testing on Oracle Database 10g as part of performance studies within the Maximum Availability Architecture (MAA) project2. For more information on MAA, please refer to [1]. For more information on Oracle 10g High Availability Practices or Data Guard, refer to [2], [3], [4] and [5]. Besides, some of these best practices were derived after extensive joint studies with real customer databases.
Tuning Media Recovery Phases
Media recovery consists of three distinct phases. Each phase must be assessed and tuned if the recovery rate is not sufficient.
1. Log Read Phase involves the reading of redo from the standby redo logs or archived redo logs by the recovery coordinator or Managed Recovery Process (MRP).
2. Redo Apply Phase involves the reading of data blocks into the buffer cache and the application of redo, by parallel recovery slave processes. The recovery coordinator (or MRP) ships redo to the recovery slaves using the parallel query (PQ) inter-process communication framework.
3. Checkpoint Phase involves the flushing to disk of modified data blocks and the update of data file headers to record checkpoint completion.
Real Application Clusters (RAC) provides additional fault tolerance to an existing Data Guard Redo Apply instance but does not help speed up recovery. For a RAC standby, in Data Guard Redo Apply, media recovery still runs on one instance, called the apply instance. However, for a RAC standby, Data Guard Broker makes it possible to achieve seamless high availability in the event of failures of one or more instances in a RAC standby. Redo transport and redo apply can be redirected to a surviving standby instance without any intervention from the user. For further details, refer to [6].
The following sections outline the best practices relevant to each phase.
Best Practices for Tuning Log Read Phase
Maximize I/O rates on standby redo logs (SRL) and archived redo logs
Measure read I/O rates on the SRL and archived redo log directories. Keep in mind that the concurrent writing of shipped redo on a standby may reduce the redo read rate due to I/O saturation. The overall recovery rate will always be bounded by the rate at which redo can be read; so ensure that the redo read rate surpasses your required recovery rate.
The following UNIX example shows how to measure the maximum redo read rate for recovery. Oracle uses a 4 MB read buffer for redo log reads.3
% /bin/time dd if=/redo_logs/t_log8.f of=/dev/null bs=4096k
50+1 records in
50+1 records out
real 6.4
user 0.0
sys 0.1
Estimated Read Rate (200 MB log file) = (50 * 4 MB) / 6.4s = 31.25 MB/sec
Best Practices for Tuning Redo Apply Phase
Assess Recovery Rate
Use the following queries to get several snapshots while a redo log is being applied to obtain the current recovery rate:
i. Determine Log Block Size (lebsz) since it is different for each operating system. This query only needs to be executed once.
select lebsz LOG_BLOCK_SIZE from x$kccle where rownum=1;
ii. Derive recovery blocks applied for at least 2 snapshots:
(a) Media Recovery Cases (e.g. recover [standby] database)
select TYPE, ITEM, SOFAR, TO_CHAR(SYSDATE, ‘DD-MON-YYYY HH:MI:SS’) TIME from v$RECOVERY_PROGRESS where ITEM=’Redo Blocks’ and TOTAL=0;
(b) Managed Recovery Cases (e.g. recover managed standby database…)
select PROCESS, SEQUENCE#, THREAD#, BLOCK#, BLOCKS, TO_CHAR(SYSDATE, ‘DD-MON-YYYY HH:MI:SS’) TIME from V$MANAGED_STANDBY where PROCESS=’MRP0’;
iii. To determine the recovery rate (MB/sec) for this archive, use one of these formulas with the information derived above:
(a) Media Recovery Case:
((SOFAR_END – SOFAR_BEG) * LOG_BLOCK_SIZE) /
((TIME_END – TIME_BEG) * 1024 * 1024 )
(b) Managed Recovery Case:
((BLOCK#_END – BLOCK#_BEG) * LOG_BLOCK_SIZE)) /
((TIME_END – TIME_BEG) * 1024 * 1024)
Use defaults for DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM
The default settings are DB_BLOCK_CHECKING = FALSE and DB_BLOCK_CHECKSUM = TRUE. Setting DB_BLOCK_CHECKING to TRUE can potentially halve the recovery rate. Block checking is always recommended on the primary database and may still be enabled on the standby if the recovery rate meets expectations. Block checksum should always be enabled for both primary and standby databases and will catch most block corruptions while incurring negligible overhead. 5
Set recovery parallelism to the number of CPUs
Parallel recovery is enabled by default for media and crash recovery with the default and optimal degree of parallelism set to the number of CPUs available. The same default settings are used by managed recovery or Data Guard Redo Apply beginning with 10.1.0.5, and for Oracle Database 10g Release 2 beginning with 10.2.0.1. Prior to these releases it is necessary to explicitly set the PARALLEL attribute in the MANAGED RECOVERY clause. For example,
RECOVER MANAGED STANDBY DATABASE PARALLEL;
If you want to experiment with an higher degree of parallelism, you can explicitly dictate the degree of parallelism.
RECOVER MANAGED STANDBY DATABASE PARALLEL <#>;
Set PARALLEL_EXECUTION_MESSAGE_SIZE (PEMS) = 8192
Increasing the PEMS parameter to 8192 may improve recovery by as much as 20%, over the default PEMS setting of 2152. The message size parameter is used by parallel query operations so there must be sufficient shared pool to support this increase. On a 64-CPU box with a 32-bit address space, an increase in message size to 8K may cause parallel query operations to fail, because of a blowout in addressable memory. Most of the recovery performance gains can be realized by increasing PEMS to 4K (4096).
Set DB_CACHE_SIZE >= Primary’s settings, DB_KEEP_CACHE_SIZE=0, and DB_RECYCLE_CACHE_SIZE=0
Having a large database cache size can improve media recovery performance significantly. Since media recovery does not require DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE or require a large SHARED_POOL_SIZE, the memory can be reallocated to the DB_CACHE_SIZE.
The only complication is resetting it to the primary database setting prior to changing to a primary role. If these parameters are different, you may require 2 initialization parameter files (init.oras or spfiles) for standby and primary roles.
Best Practices for Tuning Checkpoint Phase
Increase primary and standby log group size
Increase the primary database’s online redo log and standby database’s standby redo logs sizes to a default of 1 GB. Oracle does a full checkpoint and updates all the file headers (in an optimized manner) at each log file boundary during media recovery. To reduce the frequency of a full database checkpoint and updating all the file headers, increase the log group size so that a log switch is occurring at a minimum of 15 minutes interval6. If Real Time Apply is being used and redo is being sent synchronously or asynchronously via LGWR process, then there’s no additional data loss risk with this change. If archiver is sending the redo or the primary database is converting to ARCH mode due to heavy load, then you have to balance between faster recovery rates and higher data loss risk.
Tune I/O
DBWR needs to write out modified blocks from the buffer cache to the data files. Always use native asynchronous I/O by setting DISK_ASYNCH_IO=TRUE (default). In the rare case that asynchronous I/O is not available, use DBWR_IO_SLAVES to improve the effective data block write rate with synchronous I/O.
Ensure that you have sufficient I/O bandwidth and the I/O response time is “reasonable” for your system either by doing some base I/O tests, comparing the I/O statistics with primary, or looking at some historical I/O metrics. Be aware that I/O response time may vary when many applications share the same storage infrastructure such as with a Storage Area Network (SAN) or Network Attached Storage (NAS).
Troubleshooting and Advanced Tuning
The physical standby database or a recovery instance is unlike the primary database. While the typical primary instance may comprise of 95 percent or more query activity with many CPU intensive operations, the media recovery instance is very write and update intensive. The recovery instance’s goal is to apply changes to data blocks and write them to the data files. In many cases, the media recovery instance requires much less overall CPU resources but equal or greater I/O or memory capacity. Instead of possibly hundreds of CPU intensive operations on the primary, only the recovery coordinator (PID of the foreground process in v$process) or MRP process (MRP0 PID found in v$managed_standby) is generally CPU intensive. In many cases, fewer but faster CPUs will typically enhance recovery performance. There is no simple formula to predict the standby database system utilization. Here are some general observations that we found during our testing.
• The higher the read ratio on primary, the greater the difference in CPU utilization between the primary and standby databases.
• Higher numbers of sorts or complex queries executed will require more CPU utilization on the primary database. Queries and sorts do not create additional redo and thus do not create additional work on the standby database.
• Additional standby database CPU utilization is required when unique blocks are updated to account for the application of redo to the distinct blocks. The SQL*Loader runs modified 5 times less unique blocks compared to an OLTP run leading to 30% - 40% less CPU utilization on the standby while having almost three times the apply rate of the OLTP runs.
Therefore, tuning media recovery focuses primarily on removing system resources or database wait constraints.
Assess system resources
Use system commands such as UNIX sar and vmstat or system monitoring tools to assess system resources.
• If there are I/O bottlenecks or excessive wait I/Os, then stripe across more spindles/devices or leverage more controllers. A stripe size between 256KB to 1MB is optimal to leverage your I/O subsystem. Verify that this is not a bus or controller bottleneck or any other I/O bottleneck. The read I/O from the standby redo log should be greater than expected recovery rate.
• Check for excessive swapping or memory paging.
• Check to ensure the recovery coordinator or MRP is not CPU bound during recovery.

Failover in Oracle9i DataGuard Environments

2009-03-18T01:53:00.001-07:00

PURPOSE
-------

This Note shows the different Failover Types and Limitations for Failover in
Oracle DataGuard 9i

SCOPE & APPLICATION
-------------------

The contents of this note are valid for 9iR1 (9.0.1) and 9iR2 (9.2.0)
DataGuard Configurations, also for Logical Standby Databases. This note
should help you to understand and describe what a failover is, what
Types of Failovers are available, and how to perform them.

FAILOVER ON ORACLE DATABASES PROTECTED WITH ORACLE DATAGUARD
============================================================

1. What is a Failover ?

Oracle DataGuard is a Disaster Recovery (DR) Solution for Oracle Databases.
The basic idea behind DataGuard is a Standby Database performing all changes
from a Primary Database on itself, too. So the Standby Database should be a
constantly updated copy of a Primary Database. This copy can either be a
mounted and in Recovery mode placed (Physical Standby) or an open Database
(Logical Standby - Oracle 9iR2 only). In case of a loss of the Primary
Database, the Standby can take over the Primary Role and act as a Primary
Database. Also for planned outages of the Primary Database (eg. Hardware
upgrades,...), the Standby Database can takeover the Primary Role and
switchback to the Standby Role once the Upgrade is complete.

Please refer to the following notes for information on creating a Standby
Database:

Note 180031.1: Creating a Data Guard Configuration
Note 186150.1: Data Guard 9i Creating a Logical Standby Database

The Difference between a Switchover and a Failover is, that after a Failover,
the Standby Database becoming a Primary now, cannot switchback to become a
Standby Database again. In Opposition a Switchover exchanges the Database
Roles (The Primary becomes a Standby and the Standby becomes a Primary).
Switchovers can be performed arbitrarily, a Failover only once.

2. Failover Types

2.1 Failover on Physical Standby Database

A Failover can be performed when all or most of the information until the
Unavailability of the Primary Database was propageted to the Standby. The
usage of Standby RedoLogs ia a great advantage here. If you have no Standby
RedoLogs available, you will always encounter some Data Loss (depending on
the Changes since the latest LogSwitch). To perform a Failover just follow
these steps:

- The Primary Database is down for any reason

- Verify a Standby RedoLog is in use for Primary current Online RedoLog. You
then find in the ALERT.LOG of the Standby something like:

RFS: Successfully opened standby logfile 4:'C:\ORACLE\ORADATA\PRIMARY\STBY01.LOG'

- If this is the case run the following commands:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

This cancels the normal managed Recovery. To get the Standby RedoLog Information
is still required. Therefore issue this command:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

- If a Standby RedoLog is not used for any reason, then run this one:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH SKIP STANDBY LOGFILE;

Please keep in mind that this one causes (Minimal) Data Loss as the latestet
information from the down Primary Database is not available anymore.

- Once this is complete (This performs a complete Recovery or incomplete
Recovery until the last SCN included in the latest archived Log available at
the Standby), you can now make the Standby Database a Primary:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY

WARNING: This will only succeed if the correct RECOVER FINISH-statement was
issued before. If you forgot the 'SKIP STANDBY LOGFILE' although you
have no Standby RedoLogs, the COMMIT to Switchover will fail with the
error that more Media Recovery is required here.

- If the COMMIT TO SWITCHOVER fails for any reason you have to use the ACTIVATE
command which forces the Failover (and may cause Data Loss !!)

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

- Shutdown and restart the Databse after this command ended successfully:

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

- Now the Standby is open as a new Primary Database

2.2 Failover on a Logical Standby Database (Oracle 9iR2 only):

A Failover to a Logical Standby Database is also possible, but not recommended
for DR, as there are some restrictions on Logical Standby Databases (see
Oracle 9i DataGuard Concepts and Administration, Chapter 4.1.4 Determine
Support for Datatypes or Tables). So a Failover to a Logical Standby Database
might always cause a Data Loss.
To perform a Failover to a Logical Standby Database, perform the following
steps:

- The Primary Database is down for any reason

- Determine if all Archive Logs have been transfered from the Primary Database
to the Logical Standby Database (Either look for them in the directory and
query the DBA_LOGSTDBY_LOG-view).

- Copy all missing ArchiveLogs and the Online RedoLogs from the Primary
Database to the Logical Standby Database if possible.

- Register all copied or not already registered LogFiles to the Logical Standby
Database:

SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE '';

- Turn off Logical Apply Delay Interval (if set):

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_UNSET('APPLY_DELAY');
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

- Ensure all registered Logfiles have been applied:

SQL> SELECT APPLIED_SCN, NEWEST_SCN FROM DBA_LOGSTDBY_PROGRESS;

- Stop Logical Apply Services:

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

- Activate the Logical Standby Database:

SQL> ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE;

11g Cluster Administration and Maintenance

2009-03-16T01:13:00.000-07:00

Checking CRS Status:

The below two commands are generally used to check the status of CRS. The first command lists the status of CRS
on the local node where as the other command shows the CRS status across all the nodes in Cluster.

crsctl check crs <<-- for the local node
crsctl check cluster <<-- for remote nodes in the cluster

[root@node1-pub ~]# crsctl check crs
Cluster Synchronization Services appears healthy
Cluster Ready Services appears healthy
Event Manager appears healthy
[root@node1-pub ~]#

Checking Viability of CSS across nodes:

crsctl check cluster

For this command to run, CSS needs to be running on the local node. The "ONLINE" status for remote node says that CSS is running on that node.
When CSS is down on the remote node, the status of "OFFLINE" is displayed for that node.

[root@node1-pub ~]# crsctl check cluster
node1-pub ONLINE
node2-pub ONLINE

Viewing Cluster name:

I use below command to get the name of Cluster. You can also dump the ocr and view the name from the dump file.

ocrdump -stdout -keyname SYSTEM | grep -A 1 clustername | grep ORATEXT | awk '{print $3}'

[root@node1-pub ~]# ocrdump -stdout -keyname SYSTEM | grep -A 1 clustername | grep ORATEXT | awk '{print $3}'
test-crs
[root@node1-pub ~]#

OR

ocrconfig -export /tmp/ocr_exp.dat -s online
for i in `strings /tmp/ocr_exp.dat | grep -A 1 clustername` ; do if [ $i != 'SYSTEM.css.clustername' ]; then echo $i; fi; done

[root@node1-pub ~]# ocrconfig -export /tmp/ocr_exp.dat -s online
[root@node1-pub ~]# for i in `strings /tmp/ocr_exp.dat | grep -A 1 clustername` ; do if [ $i != 'SYSTEM.css.clustername' ]; then echo $i; fi; done
test-crs
[root@node1-pub ~]#

OR

Oracle creates a directory with the same name as Cluster under the $ORA_CRS_HOME/cdata. you can get the cluster name from this directory as well.

[root@node1-pub ~]# ls /u01/app/crs/cdata
localhost test-crs

Viewing No. Of Nodes configured in Cluster:

The below command can be used to find out the number of nodes registered into the cluster.
It also displays the node's Public name, Private name and Virtual name along with their numbers.

olsnodes -n -p -i

[root@node1-pub ~]# olsnodes -n -p -i
node1-pub 1 node1-prv node1-vip
node2-pub 2 node2-prv node2-vip

Viewing Votedisk Information:

The below command is used to view the no. of Votedisks configured in the Cluster.

crsctl query css votedisk

[root@node1-pub ~]# crsctl query css votedisk
0. 0 /u02/ocfs2/vote/VDFile_0
1. 0 /u02/ocfs2/vote/VDFile_1
2. 0 /u02/ocfs2/vote/VDFile_2
Located 3 voting disk(s).
[root@node1-pub ~]#

[root@node1-pub ~]# crsctl check crs
Cluster Synchronization Services appears healthy
Cluster Ready Services appears healthy
Event Manager appears healthy
[root@node1-pub ~]#

Viewing OCR Disk Information:

The below command is used to view the no. of OCR files configured in the Cluster. It also displays the version of OCR
as well as storage space information. You can only have 2 OCR files at max.

ocrcheck

[root@node1-pub ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262120
Used space (kbytes) : 3848
Available space (kbytes) : 258272
ID : 744414276
Device/File Name : /u02/ocfs2/ocr/OCRfile_0
Device/File integrity check succeeded
Device/File Name : /u02/ocfs2/ocr/OCRfile_1
Device/File integrity check succeeded

Cluster registry integrity check succeeded

Various Timeout Settings in Cluster:

Disktimeout:
Disk Latencies in seconds from node-to-Votedisk. Default Value is 200. (Disk IO)
Misscount:
Network Latencies in second from node-to-node (Interconnect). Default Value is 60 Sec (Linux) and 30 Sec in Unix platform. (Network IO)
Misscount < Disktimeout

NOTE: Do not change them without contacting Oracle Support. This may cause logical corruption to the Data.

IF
(Disk IO Time > Disktimeout) OR (Network IO time > Misscount)
THEN
REBOOT NODE
ELSE
DO NOT REBOOT
END IF;

crsctl get css disktimeout
crsctl get css misscount
crsctl get css reboottime

[root@node1-pub ~]# crsctl get css disktimeout
200

[root@node1-pub ~]# crsctl get css misscount
Configuration parameter misscount is not defined. <<<<< This message indicates that the Misscount is not set maually and it is set to it's
Default Value On Linux, it is default to 60 Second. If you want to chang it, you can do that as below. (Not recommended)

[root@node1-pub ~]# crsctl set css misscount 100
Configuration parameter misscount is now set to 100.
[root@node1-pub ~]# crsctl get css misscount
100

The below command sets the value of misscount back to its Default values:

crsctl unset css misscount

[root@node1-pub ~]# crsctl unset css misscount

[root@node1-pub ~]# crsctl get css reboottime
3

Add/Remove OCR file in Cluster:

Removing OCR File

(1) Get the Existing OCR file information by running ocrcheck utility.

[root@node1-pub ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262120
Used space (kbytes) : 3852
Available space (kbytes) : 258268
ID : 744414276
Device/File Name : /u02/ocfs2/ocr/OCRfile_0 <-- OCR
Device/File integrity check succeeded
Device/File Name : /u02/ocfs2/ocr/OCRfile_1 <-- OCR Mirror
Device/File integrity check succeeded

Cluster registry integrity check succeeded

(2) The First command removes the OCR mirror (/u02/ocfs2/ocr/OCRfile_1). If you want to remove the OCR
file (/u02/ocfs2/ocr/OCRfile_1) run the next command.

ocrconfig -replace ocrmirror
ocrconfig -replace ocr

[root@node1-pub ~]# ocrconfig -replace ocrmirror
[root@node1-pub ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262120
Used space (kbytes) : 3852
Available space (kbytes) : 258268
ID : 744414276
Device/File Name : /u02/ocfs2/ocr/OCRfile_0 <<-- OCR File
Device/File integrity check succeeded

Device/File not configured <-- OCR Mirror not existed any more

Cluster registry integrity check succeeded

Adding OCR

You need to add OCR or OCR Mirror file in a case where you want to move the existing OCR file location to the different Devices.
The below command add ths OCR mirror file if OCR file alread exists.

(1) Get the Current status of OCR:

[root@node1-pub ~]# ocrconfig -replace ocrmirror
[root@node1-pub ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262120
Used space (kbytes) : 3852
Available space (kbytes) : 258268
ID : 744414276
Device/File Name : /u02/ocfs2/ocr/OCRfile_0 <<-- OCR File
Device/File integrity check succeeded

Device/File not configured <-- OCR Mirror does not exist

Cluster registry integrity check succeeded

As You can see, I only have one OCR file but not the second file which is OCR Mirror.
So, I can add second OCR (OCR Mirror) as below command.

ocrconfig -replace ocrmirror

[root@node1-pub ~]# ocrconfig -replace ocrmirror /u02/ocfs2/ocr/OCRfile_1
[root@node1-pub ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262120
Used space (kbytes) : 3852
Available space (kbytes) : 258268
ID : 744414276
Device/File Name : /u02/ocfs2/ocr/OCRfile_0
Device/File integrity check succeeded
Device/File Name : /u02/ocfs2/ocr/OCRfile_1
Device/File integrity check succeeded

Cluster registry integrity check succeeded

You can have at most 2 OCR devices (OCR itself and its single Mirror) in a cluster. Adding extra Mirror gives you below error message

[root@node1-pub ~]# ocrconfig -replace ocrmirror /u02/ocfs2/ocr/OCRfile_2
PROT-21: Invalid parameter
[root@node1-pub ~]#

Add/Remove Votedisk file in Cluster:

Adding Votedisk:

Get the existing Vote Disks associated into the cluster. To be safe, Bring crs cluster stack down on all the nodes
but one on which you are going to add votedisk from.

(1) Stop CRS on all the nodes in cluster but one.

[root@node2-pub ~]# crsctl stop crs

(2) Get the list of Existing Vote Disks

crsctl query css votedisk

[root@node1-pub ~]# crsctl query css votedisk
0. 0 /u02/ocfs2/vote/VDFile_0
1. 0 /u02/ocfs2/vote/VDFile_1
2. 0 /u02/ocfs2/vote/VDFile_2
Located 3 voting disk(s).

(3) Backup the VoteDisk file

Backup the existing votedisks as below as oracle:

dd if=/u02/ocfs2/vote/VDFile_0 of=$ORACLE_BASE/bkp/vd/VDFile_0

[root@node1-pub ~]# su - oracle
[oracle@node1-pub ~]$ dd if=/u02/ocfs2/vote/VDFile_0 of=$ORACLE_BASE/bkp/vd/VDFile_0
41024+0 records in
41024+0 records out
[oracle@node1-pub ~]$

(4) Add an Extra Votedisk into the Cluster:

If it is a OCFS, then touch the file as oracle. On raw devices, initialize the raw devices using "dd" command

touch /u02/ocfs2/vote/VDFile_3 <<-- as oracle
crsctl add css votedisk /u02/ocfs2/vote/VDFile_3 <<-- as oracle
crsctl query css votedisks

[root@node1-pub ~]# su - oracle
[oracle@node1-pub ~]$ touch /u02/ocfs2/vote/VDFile_3
[oracle@node1-pub ~]$ crsctl add css votedisk /u02/ocfs2/vote/VDFile_3
Now formatting voting disk: /u02/ocfs2/vote/VDFile_3.
Successful addition of voting disk /u02/ocfs2/vote/VDFile_3.

(5) Confirm that the file has been added successfully:

[root@node1-pub ~]# ls -l /u02/ocfs2/vote/VDFile_3
-rw-r----- 1 oracle oinstall 21004288 Oct 6 16:31 /u02/ocfs2/vote/VDFile_3
[root@node1-pub ~]# crsctl query css votedisks
Unknown parameter: votedisks
[root@node1-pub ~]# crsctl query css votedisk
0. 0 /u02/ocfs2/vote/VDFile_0
1. 0 /u02/ocfs2/vote/VDFile_1
2. 0 /u02/ocfs2/vote/VDFile_2
3. 0 /u02/ocfs2/vote/VDFile_3
Located 4 voting disk(s).
[root@node1-pub ~]#

Removing Votedisk:

Removing Votedisk from the cluster is very simple. Tthe below command removes the given votedisk from cluster configuration.

crsctl delete css votedisk /u02/ocfs2/vote/VDFile_3

[root@node1-pub ~]# crsctl delete css votedisk /u02/ocfs2/vote/VDFile_3
Successful deletion of voting disk /u02/ocfs2/vote/VDFile_3.
[root@node1-pub ~]#

[root@node1-pub ~]# crsctl query css votedisk
0. 0 /u02/ocfs2/vote/VDFile_0
1. 0 /u02/ocfs2/vote/VDFile_1
2. 0 /u02/ocfs2/vote/VDFile_2
Located 3 voting disk(s).
[root@node1-pub ~]#

Backing Up OCR

Oracle performs physical backup of OCR devices every 4 hours under the default backup direcory $ORA_CRS_HOME/cdata/
and then it rolls that forward to Daily, weekly and monthly backup. You can get the backup information by executing below command.

ocrconfig -showbackup

[root@node1-pub ~]# ocrconfig -showbackup

node2-pub 2007/09/03 17:46:47 /u01/app/crs/cdata/test-crs/backup00.ocr

node2-pub 2007/09/03 13:46:45 /u01/app/crs/cdata/test-crs/backup01.ocr

node2-pub 2007/09/03 09:46:44 /u01/app/crs/cdata/test-crs/backup02.ocr

node2-pub 2007/09/03 01:46:39 /u01/app/crs/cdata/test-crs/day.ocr

node2-pub 2007/09/03 01:46:39 /u01/app/crs/cdata/test-crs/week.ocr
[root@node1-pub ~]#

Manually backing up the OCR

ocrconfig -manualbackup <<--Physical Backup of OCR

The above command backs up OCR under the default Backup directory. You can export the contents of the OCR using below command (Logical backup).

ocrconfig -export /tmp/ocr_exp.dat -s online <<-- Logical Backup of OCR

Restoring OCR

The below command is used to restore the OCR from the physical backup. Shutdown CRS on all nodes.

ocrconfig -restore

Locate the avialable Backups

[root@node1-pub ~]# ocrconfig -showbackup

node2-pub 2007/09/03 17:46:47 /u01/app/crs/cdata/test-crs/backup00.ocr

node2-pub 2007/09/03 13:46:45 /u01/app/crs/cdata/test-crs/backup01.ocr

node2-pub 2007/09/03 09:46:44 /u01/app/crs/cdata/test-crs/backup02.ocr

node2-pub 2007/09/03 01:46:39 /u01/app/crs/cdata/test-crs/day.ocr

node2-pub 2007/09/03 01:46:39 /u01/app/crs/cdata/test-crs/week.ocr

node1-pub 2007/10/07 13:50:41 /u01/app/crs/cdata/test-crs/backup_20071007_135041.ocr

Perform Restore from previous Backup

[root@node2-pub ~]# ocrconfig -restore /u01/app/crs/cdata/test-crs/week.ocr

The above command restore the OCR from week old backup.
If you have logical backup of OCR (taken using export option), then You can import it with the below command.

ocrconfig -import /tmp/ocr_exp.dat

Restoring Votedisks

Shutdown CRS on all the nodes in Cluster.
Locate the current location of the Votedisks
Restore each of the votedisks using "dd" command from the previous good backup of Votedisk taken using the same "dd" command.
Start CRS on all the nodes.
crsctl stop crs
crsctl query css votedisk
dd if= of= <<-- do this for all the votedisks
crsctl start crs

Changing Public and Virtual IP Address:

Current Config Changed to

Node 1:

Public IP: 216.160.37.154 192.168.10.11
VIP: 216.160.37.153 192.168.10.111
subnet: 216.160.37.159 192.168.10.0
Netmask: 255.255.255.248 255.255.255.0
Interface used: eth0 eth0
Hostname: node1-pub.hingu.net node1-pub.hingu.net

Node 2:

Public IP: 216.160.37.156 192.168.10.22
VIP: 216.160.37.157 192.168.10.222
subnet: 216.160.37.159 192.168.10.0
Netmask: 255.255.255.248 255.255.255.0
Interface used: eth0 eth0
Hostname: node1-pub.hingu.net node2-pub.hingu.net

=======================================================================
(A)

Take the Services, Database, ASM Instances and nodeapps down on both the Nodes in Cluster. Also disable the nodeapps, asm and database instances to prevent them from restarting in case if this node gets rebooted during this process.

srvctl stop service -d test
srvctl stop database -d test
srvctl stop asm -n node1-pub
srvctl stop asm -n node2-pub
srvctl stop nodeapps -n node1-pub,node1-pub2
srvctl disable instance -d test -i test1,test2
srvctl disable asm -n node1-pub
srvctl disable asm -n node2-pub
srvctl disable nodeapps -n node1-pub
srvctl disable nodeapps -n node2-pub

(B)
Modify the /etc/hosts and/or DNS, ifcfg-eth0 (local node) with the new IP values
on All the Nodes

(C)
Restart the specific network interface in order to use the new IP.

ifconfig eth0 down
ifconfig eth0 up

Or, you can restart the network.
CAUTION: on NAS, restarting entire network may cause the node to be rebooted.

(D)
Update the OCR with the New Public IP.
In case of public IP, you have to delete the interface first and then add it back with the new IP address.

As oracle user, Issue the below command:

oifcfg delif -global eth0
oifcfg setif -global eth0/192.168.10.0:public

(E)
Update the OCR with the New Virtual IP.
Virtual IP is part of the nodeapps and so you can modify the nodeapps to update the Virtual IP information.

As privileged user (root), Issue the below commands:

srvctl modify nodeapps -n node1-pub -A 192.168.10.111/255.255.255.0/eth0 <-- for Node 1
srvctl modify nodeapps -n node1-pub -A 192.168.10.222/255.255.255.0/eth0 <-- for Node 2

(F)
Enable the nodeapps, ASM, database Instances for all the Nodes.

srvctl enable instance -d test -i test1,test2
srvctl enable asm -n node1-pub
srvctl enable asm -n node2-pub
srvctl enable nodeapps -n node1-pub
srvctl enable nodeapps -n node2-pub

(G)
Update the listener.ora file on each nodes with the correct IP addresses in case if it uses the IP address instead of the hostname.

(H)
Restart the Nodeapps, ASM and Database instance

srvctl start nodeapps -n node1-pub
srvctl start nodeapps -n node2-pub
srvctl start asm -n node1-pub
srvctl start asm -n node2-pub
srvctl start database -d test

=======================================================================

Step-By-Step Installation of Oracle 9i RAC on IBM AIX

2009-03-16T01:03:00.001-07:00

3.0 Preparing for the installation of RAC

The Real Application Clusters installation process includes four major tasks.

Configure the shared disks and UNIX preinstallation tasks.
Run the Oracle Universal Installer to install the Oracle9i Enterprise Edition and the Oracle9i Real Application Clusters software.
Create and configure your database.

3.1 Configure the shared disks and UNIX preinstallation tasks

3.1.1 Configure the shared disks

Real Application Clusters requires that all each instance be able to access a set of unformatted devices on a shared disk subsystem if GPFS is not being used. These shared disks are also referred to as raw devices. If your platform supports an Oracle-certified cluster file system, however, you can store the files that Real Application Clusters requires directly on the cluster file system.

Note: If you are using Parallel Filesystem (GPFS), however, you can store the files that Real Application Clusters requires
directly on the cluster file system !

The Oracle instances in Real Application Clusters write data onto the raw devices to update the control file, server parameter file, each datafile, and each redo log file. All instances in the cluster share these files.

The Oracle instances in the RAC configuration write information to raw devices defined for:

The control file
The spfile.ora
Each datafile
Each ONLINE redo log file
Server Manager (SRVM) configuration information
It is therefore necessary to define raw devices for each of these categories of file. The Oracle Database Configuration Assistant (DBCA) will create a seed database expecting the following configuration:

Raw Volume File Size Sample File Name
SYSTEM tablespace 400 Mb db_name_raw_system_400m
USERS tablespace 120 Mb db_name_raw_users_120m
TEMP tablespace 100 Mb db_name_raw_temp_100m
UNDOTBS tablespace per instance 312 Mb db_name_raw_undotbsx_312m
CWMLITE tablespace 100 Mb db_name_raw_cwmlite_100m
EXAMPLE 160 Mb db_name_raw_example_160m
OEMREPO 20 Mb db_name_raw_oemrepo_20m
INDX tablespace 70 Mb db_name_raw_indx_70m
TOOLS tablespace 12 Mb db_name_raw_tools_12m
DRYSYS tablespace 90 Mb db_name_raw_drsys_90m
First control file 110 Mb db_name_raw_controlfile1_110m
Second control file 110 Mb db_name_raw_controlfile2_110m
Two ONLINE redo log files per instance 120 Mb x 2 db_name_thread_lognumber_120m
spfile.ora 5 Mb db_name_raw_spfile_5m
srvmconfig 100 Mb db_name_raw_srvmconf_100m

Note: Automatic Undo Management requires an undo tablespace per instance therefore you would require a minimum of 2 tablespaces as described above. By following the naming convention described in the table above, raw partitions are identified with the database and the raw volume type (the data contained in the raw volume). Raw volume size is also identified using this method.

Note: In the sample names listed in the table, the string db_name should be replaced with the actual database name, thread is the thread number of the instance, and lognumber is the log number within a thread.

On the node from which you run the Oracle Universal Installer, create an ASCII file identifying the raw volume objects as shown above. The
DBCA requires that these objects exist during installation and database creation. When creating the ASCII file content for the objects, name them
using the format:

database_object=raw_device_file_path

When you create the ASCII file, separate the database objects from the paths with equals (=) signs as shown in the example below:

system1=/dev/rdb_name_system_400m
spfile1=/dev/rdb_name_spfile_5m
users1=/dev/rdb_name_users_120m
temp1=/dev/rdb_name_emp_100m
undotbs1=/dev/rdb_name_undotbs1_312m
undotbs2=/dev/rdb_name_undotbs2_312m
example1=/dev/rdb_name_example_160m
cwmlite1=/dev/rdb_name_cwmlite_100m
indx1=/dev/rdb_name_indx_70m
tools1=/dev/rdb_name_tools_12m
drsys1=/dev/rdb_name_drsys_90m
control1=/dev/rdb_name_cntrl1_110m
control2=/dev/rdb_name_cntrl2_110m
redo1_1=/dev/rdb_name_log11_120m
redo1_2=/dev/rdb_name_log12_120m
redo2_1=/dev/rdb_name_log21_120m
redo2_2=/dev/rdb_name_log22_120m

You must specify that Oracle should use this file to determine the raw device volume names by setting the following environment variable where filename
is the name of the ASCII file that contains the entries shown in the example above:

csh:

setenv DBCA_RAW_CONFIG filename

ksh, bash or sh:

DBCA_RAW_CONFIG=filename; export DBCA_RAW_CONFIG

3.1.2 UNIX Preinstallation Steps

Note: In addition, you can run the installPrep.sh script provided in Note 189256.1 which catches most unix environment problems.

After configuring the raw volumes, perform the following steps prior to installation as root user:

Add the Oracle USER

Make sure you have an osdba group defined in the /etc/group file on all nodes of your cluster. To designate an osdba group name and group number and osoper group during installation, these group names must be identical on all nodes of your UNIX cluster that will be part of the Real Application Clusters database. The default UNIX group name for the osdba and osoper groups is dba. There also needs be an oinstall group which the software owner should have as its primary group. A typical entry would therefore look like the following:
dba::101:oracle
oinstall::102:root,oracle

The following is an example of the command used to create the "dba" group with a group ID of "101":

# mkgroup -'A' id='101' users='oracle' dba

Create an oracle account on each node so that the account:
Is a member of the osdba group (dba in this example)
Has oinstall as its primary group
Is used only to install and update Oracle software
Has write permissions on remote directories
The following is an example of the smit command used to create the "oracle" user:

Smit -> Security & Users -> Users -> Add a User

Fastpath:

# smit mkuser

Type or select values in entry fields.Press Enter AFTER making all desired changes.[TOP]
[Entry Fields]* User NAME [oracle] User
ID [101] # ADMINISTRATIVE USER? false + Primary GROUP [oinstall] +
Group SET [] +
ADMINISTRATIVE GROUPS [] +
ROLES [] +
Another user can SU TO USER? true +
SU GROUPS [ALL] +
HOME directory [/home/oracle] Initial PROGRAM [/bin/ksh]
User INFORMATION []
EXPIRATION date (MMDDhhmmyy) [0]

Note that the primary group is not "dba". The "use" of "oinstall" is optional but recommended. For more information on the use of the "oinstall" group see the : Oracle9i Installation Guide Release 2 (9.X.X.X.0) for UNIX Systems: AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel and Sun SPARC Solaris documentation.

Create a mount point directory on each node to serve as the top of your Oracle software directory structure so that:
The name of the mount point on each node is identical to that on the initial node
The oracle account has read, write, and execute privileges
On the node from which you will run the Oracle Universal Installer, set up user equivalence by adding entries for all nodes in the cluster, including the local node, to the .rhosts file of the oracle account, or the /etc/hosts.equiv file.
As oracle account user, check for user equivalence for the oracle account by performing a remote login (rlogin) to each node in the cluster.
As oracle account user, if you are prompted for a password, you have not given the oracle account the same attributes on all nodes. You must
correct this because the Oracle Universal Installer cannot use the rcp command to copy Oracle products to the remote node's directories without
user equivalence.
Establish system environment variables

Set a local bin directory in the user's PATH, such as /usr/local/bin, or /opt/bin. It is necessary to have execute permissions on this directory.
Set the DISPLAY variable to point to the system's (from where you will run OUI) IP address, or name, X server, and screen.
Set a temporary directory path for TMPDIR with at least 20 Mb of free space to which the OUI has write permission.
Establish Oracle environment variables: Set the following Oracle environment variables:

Environment Variable Suggested value
ORACLE_BASE eg /u01/app/oracle
ORACLE_HOME eg /u01/app/oracle/product/901
ORACLE_TERM xterm
NLS_LANG AMERICAN-AMERICA.UTF8 for example
ORA_NLS33 $ORACLE_HOME/ocommon/nls/admin/data
PATH Should contain $ORACLE_HOME/bin
CLASSPATH $ORACLE_HOME/JRE:$ORACLE_HOME/jlib \
$ORACLE_HOME/rdbms/jlib: \
$ORACLE_HOME/network/jlib

Create the directory /var/opt/oracle and set ownership to the oracle user.
Note: There is a verification script InstallPrep.sh available which may be downloaded and run prior to the installation of Oracle Real Application Clusters. This script verifies that the system is configured correctly according to the Installation Guide. The output of the script will report any further tasks that need to be performed before successfully installing Oracle 9.x DataServer (RDBMS). This script performs the following verifications:-

ORACLE_HOME Directory Verification
UNIX User/umask Verification
UNIX Group Verification
Memory/Swap Verification
TMP Space Verification
Real Application Cluster Option Verification
Unix Kernel Verification

./InstallPrep.sh
You are currently logged on as oracle
Is oracle the unix user that will be installing Oracle Software? y or n
y
Enter the unix group that will be used during the installation
Default: dba
dba

Enter Location where you will be installing Oracle
Default: /u01/app/oracle/product/oracle9i
/u01/app/oracle/product/9.2.0.1
Your Operating System is AIX
Gathering information... Please wait

Checking unix user ...
user test passed

Checking unix umask ...
umask test passed

Checking unix group ...
Unix Group test passed

Checking Memory & Swap...
Memory test passed

/tmp test passed

Checking for a cluster...
AIX Cluster test
Cluster has been detected
You have 2 cluster members configured and 2 are curently up
No cluster warnings detected
Processing kernel parameters... Please wait
Running Kernel Parameter Report...
Check the report for Kernel parameter verification

Completed.

/tmp/Oracle_InstallPrep_Report has been generated

Please review this report and resolve all issues before attempting to install the Oracle Database Software

3.2 Using the Oracle Universal Installer for Real Application Clusters
Follow these procedures to use the Oracle Universal Installer to install the Oracle Enterprise Edition and the Real Application Clusters software. Oracle9i is supplied on multiple CD-ROM disks. During the installation process it is necessary to switch between the CD-ROMS. OUI will manage the switching between CDs. For the latest RAC/IBM certification matrix see here.

To install the Oracle Software, perform the following:.
Login as the root user and mount the first CD-ROM if installing from CD-ROM
# mount -rv cdrfs /dev/cd0 /cdrom

Execute the "rootpre.sh" shell script on the CD-ROM mount point or the location of Disk1 if installing from a disk stage. See the Oracle9i Installation Guide Release 2 (9.X.X.X.0) for UNIX Systems: AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel and Sun SPARC Solaris documentation for more information on creating disk stages.

# //rootpre.sh
Login as the oracle user and execute the "runInstaller". See if you experience problems starting the runInstaller.

$ //runInstaller

At the OUI Welcome screen, click Next.
A prompt will appear for the Inventory Location (if this is the first time that OUI has been run on this system). This is the base directory into which OUI will install files. The Oracle Inventory definition can be found in the file /etc/oraInst.loc. Click OK.
Verify the UNIX group name of the user who controls the installation of the Oracle9i software. If an instruction to run /tmp/orainstRoot.sh appears, the pre-installation steps were not completed successfully. Typically, the /var/opt/oracle directory does not exist or is not writeable by oracle. Run /tmp/orainstRoot.sh to correct this, forcing Oracle Inventory files, and others, to be written to the ORACLE_HOME directory. Once again this screen only appears the first time Oracle9i products are installed on the system. Click Next.
The File Location window will appear. Do NOT change the Source field. The Destination field defaults to the ORACLE_HOME environment variable. Click Next.
Select the Products to install. In this example, select the Oracle9i Server then click Next.
Select the installation type. Choose the Enterprise Edition option. The selection on this screen refers to the installation operation, not the database configuration. The next screen allows for a customized database configuration to be chosen. Click Next.
Select the configuration type. In this example you choose the Advanced Configuration as this option provides a database that you can customize, and configures the selected server products. Select Customized and click Next.
Select the other nodes on to which the Oracle RDBMS software will be installed. It is not necessary to select the node on which the OUI is currently running. Click Next.

NOTE: If choosing to create a shared Oracle Home on GPFS, you will only choose one node within the Cluster Node Selection screen of OUI for the installation of the binaries.
Identify the raw partition in to which the Oracle9i Real Application Clusters (RAC) configuration information will be written. It is recommended that this raw partition is a minimum of 100MB in size.
An option to Upgrade or Migrate an existing database is presented. Do NOT select the radio button. The Oracle Migration utility is not able to upgrade a RAC database, and will error if selected to do so.
The Summary screen will be presented. Confirm that the RAC database software will be installed and then click Install. The OUI will install the Oracle9i software on to the local node, and then copy this information to the other nodes selected.
Once Install is selected, the OUI will install the Oracle RAC software on to the local node, and then copy software to the other nodes selected earlier. This will take some time. During the installation process, the OUI does not display messages indicating that components are being installed on other nodes - I/O activity may be the only indication that the process is continuing.
3.3 Create a RAC Database using the Oracle Database Configuration Assistant

The Oracle Database Configuration Assistant (DBCA) will create a database for you (for an example of manual database creation see Database Creation in Oracle9i RAC). The DBCA creates your database using the optimal flexible architecture (OFA). This means the DBCA creates your database files, including the default server parameter file, using standard file naming and file placement practices. The primary phases of DBCA processing are:-

Verify that you correctly configured the shared disks for each tablespace (for non-cluster file system platforms)
Create the database
Configure the Oracle network services
Start the database instances and listeners
Oracle Corporation recommends that you use the DBCA to create your database. This is because the DBCA preconfigured databases optimize your environment to take advantage of Oracle9i features such as the server parameter file and automatic undo management. The DBCA also enables you to
define arbitrary tablespaces as part of the database creation process. So even if you have datafile requirements that differ from those offered in one of the DBCA templates, use the DBCA. You can also execute user-specified scripts as part of the database creation process.

The DBCA and the Oracle Net Configuration Assistant also accurately configure your Real Application Clusters environment for various Oracle high availability features and cluster administration tools.

DBCA will launch as part of the installation process, but can be run manually by executing the command dbca from the $ORACLE_HOME/bin directory on UNIX platforms. The RAC Welcome Page displays. Choose Oracle Cluster Database option and select Next.
The Operations page is displayed. Choose the option Create a Database and click Next.
The Node Selection page appears. Select the nodes that you want to configure as part of the RAC database and click Next. If nodes are missing from the Node Selection then perform clusterware diagnostics by executing the $ORACLE_HOME/bin/lsnodes -v command and analyzing its output. Refer to your vendor's clusterware documentation if the output indicates that your clusterware is not properly installed. Resolve the problem and then restart the DBCA.
The Database Templates page is displayed. The templates other than New Database include datafiles. Choose New Database and then click Next.
The Show Details button provides information on the database template selected.
DBCA now displays the Database Identification page. Enter the Global Database Name and Oracle System Identifier (SID). The Global Database Name is typically of the form name.domain, for example mydb.us.oracle.com while the SID is used to uniquely identify an instance (DBCA should insert a suggested SID, equivalent to name1 where name was entered in the Database Name field). In the RAC case the SID specified will be used as a prefix for the instance number. For example, MYDB, would become MYDB1, MYDB2 for instance 1 and 2 respectively.
The Database Options page is displayed. Select the options you wish to configure and then choose Next. Note: If you did not choose New Database from the Database Template page, you will not see this screen.
The Additional database Configurations button displays additional database features. Make sure both are checked and click OK.
Select the connection options desired from the Database Connection Options page. Note: If you did not choose New Database from the Database Template page, you will not see this screen. Click Next.
DBCA now displays the Initialization Parameters page. This page comprises a number of Tab fields. Modify the Memory settings if desired and then select the File Locations tab to update information on the Initialization Parameters filename and location. Then click Next.
The option Create persistent initialization parameter file is selected by default. If you have a cluster file system, then enter a file system name, otherwise a raw device name for the location of the server parameter file (spfile) must be entered. Then click Next.
The button File Location Variables… displays variable information. Click OK.
The button All Initialization Parameters… displays the Initialization Parameters dialog box. This box presents values for all initialization parameters and indicates whether they are to be included in the spfile to be created through the check box, included (Y/N). Instance specific parameters have an instance value in the instance column. Complete entries in the All Initialization Parameters page and select Close. Note: There are a few exceptions to what can be altered via this screen. Ensure all entries in the Initialization Parameters page are complete and select Next.
DBCA now displays the Database Storage Window. This page allows you to enter file names for each tablespace in your database.
The file names are displayed in the Datafiles folder, but are entered by selecting the Tablespaces icon, and then selecting the tablespace object from the expanded tree. Any names displayed here can be changed. A configuration file can be used, see section 3.2.1, (pointed to by the environment variable DBCA_RAW_CONFIG). Complete the database storage information and click Next.
The Database Creation Options page is displayed. Ensure that the option Create Database is checked and click Finish.
The DBCA Summary window is displayed. Review this information and then click OK.
Once the Summary screen is closed using the OK option, DBCA begins to create the database according to the values specified.
A new database now exists. It can be accessed via Oracle SQL*PLUS or other applications designed to work with an Oracle RAC database.

5.0 References

Note: 182037.1 - AIX: Quick Start Guide - 9.0.1 RDBMS Installation
Note: 201019.1 - AIX: Quick Start Guide - 9.2.0 RDBMS Installation
Note:137288.1 - Database Creation in Oracle9i RAC
Note:183408.1 - Raw Devices and Cluster Filesystems With Real Application Clusters
RAC/IBM AIX certification matrix
Oracle9i Real Application Clusters Installation and Configuration Release 1 (9.0.1)
Oracle9i Real Application Clusters Concepts
Oracle9i Real Application Clusters Administration
Oracle9i Real Application Clusters Deployment and Performance
Oracle9i Installation Guide for Compaq Tru64, Hewlett-Packard HPUX, IBM-AIX, Linux, and Sun Solaris-based systems.
Oracle9i Release Notes

Complete Checklist for Manual Upgrades to 11gR1 - ML Note:429825.1

2009-03-03T05:37:00.000-08:00

11gR1 manual upgrade checklist
Metalink Note : Note:429825.1. (latest revision on 05-MAR-2008)

Solaris Kernel Configuration - Oracle

2008-11-16T01:41:00.000-08:00

Solaris Kernel Configuration - Oracle

Sample Kernel Configuration:
Parameter Description Formula Value
SHMMAX Maximum Shared Memory Segment Size - 4,294,967,295
SHMMIN Minimum Shared Memory Segment Size - 1
SHMMNI Maximum Number of Shared Memory Identifers - 100
SHMSEG Maximum Number of Shared Memory Segments a user process can attach - 10
SEMMNI Maximum Number of Semaphore Identifiers - 100
SEMMSL Maximum Number of Semaphores in a Set 10 + max(P[db1],P[db2],…,P[dbn]) = 10 + max(350,35) = 10+ 350 = 360
SEMMNS Maximum Number of Semaphores in a System (P[db1] + P[db2] + … + P[dbn]) + max(P[db1],P[db2],…,P[dbn]) + 10 * count(P[db1],P[db2],…,P[dbn])
= (350+35) + max(350,35) + 10(2) = 385 + 350 + 20 = 755
SEMOPM Maximum Number of Operations per SEMOP call - 100
SEMVMX Semaphore Maximum Value - 32,767

Sample Databases:
Processes Value
P[db1] 350
P[db2] 35

Sample of /etc/system Parameter:
************************************************************************************
* B E G I N O R A C L E C O N F I G U R A T I O N
************************************************************************************

set shmsys: shminfo_shmmax = 4294967295

*
* Shared Memory Parameters - Static
*
set shmsys: shminfo_shmmin = 1
set shmsys: shminfo_shmmni = 100
set shmsys: shminfo_shmseg = 10
*
* Semaphore parameters - semmsl & semmns are dynamic:
*
* semmsl := 10 + max(P[db1],P[db2],...,P[dbn])
* semmns := (P[db1]+P[db2]+...+P[dbn]) + max(P[db1],P[db2],...,P[dbn]) + 10 * count (P[db1],P[db2],...,P[dbn])
*
* where P[dbX] is the processes parameter set for an Oracle database X
*
set semsys: seminfo_semmni = 100
set semsys: seminfo_semmsl = 360
set semsys: seminfo_semmns = 755
set semsys: seminfo_semopm = 100
set semsys: seminfo_semvmx = 32767

*
* Increase file descriptor limits (large databases)
*
set rlim_fd_max=8192
set rlim_fd_cur=1024

************************************************************************************
* E N D O R A C L E C O N F I G U R A T I O N
************************************************************************************

--------------------------------------------------------------------------------
OCPdba.Net

Think Outside the Box

2008-11-05T05:52:00.000-08:00

You are driving along in your car on a wild, stormy night, it’s raining heavily, when suddenly you pass by a bus stop, and you see three people waiting for a bus:

An old lady who looks as if she is about to die.

An old friend who once saved your life.

The perfect partner you have been dreaming about.

Which one would you choose to offer a ride to, knowing very well that there could only be one passenger in your car?

This is a dilemma that was once used as part of a job application.

You could pick up the old lady, because she is going to die, and thus you
should save her first;

* or you could take the old friend because he once saved your life, and this
would be the perfect chance to ! pay him back.

However, you may never be able to find your perfect mate again.

The candidate who was hired had no trouble coming up with his answer. Guess what was his answer?

He simply answered:

“I would give the car keys to my Old friend and let him take the lady to the hospital. I would stay behind and wait for the bus with the partner of my dreams.”

Sometimes, we gain more if we are able to give up our stubborn thought limitations. Never forget to “Think Outside the Box.”

Rename datafile (file system to raw device AIX) ,database archivelog mode

2008-10-25T02:19:00.001-07:00

1) Take the data file offline
ALTER DATABASE DATAFILE '/oracle/pindb/arch/data/rpindb1_pinx112.dbf' OFFLINE;
2) Copy the data file to new raw device
dd if=/oracle/pindb/arch/data/rpindb1_pinx112.dbf of=/dev/rpindb1_pinx113 bs=4096 seek=1
3) rename file
ALTER DATABASE RENAME FILE '/oracle/pindb/arch/data/rpindb1_pinx112.dbf' TO '/dev/rpindb1_pinx113';
4) RECOVER DATAFILE '/dev/rpindb1_pinx113';
5) ALTER DATABASE DATAFILE '/dev/rpindb1_pinx113' online;

13 Behaviors that are common to high trust leaders and people throughout the world.

2008-10-21T23:59:00.000-07:00

Character-Based Behaviors

Behavior #1: Talk Straight. Communicate clearly so that you cannot be misunderstood. Preface your discussions by declaring your intent, so you leave no doubt about what you are thinking. Counterfeit behaviors include withholding information, flattery and spin. Be honest and call things what they are. Don’t manipulate people, distort facts or leave false impressions.

Behavior #2: Demonstrate Respect. This behavior is based on the principles of respect, fairness, kindness, love and civility. The opposite is commonly experienced as showing disrespect, which is a huge issue, both at work and at home. The counterfeit is to fake respect or concern, or, most insidious of all, to show respect and concern for only those who can do something for you.

Behavior #3: Create Transparency. Be real and genuine and tell the truth in a way that people can verify. The opposite is to obscure, and the counterfeit is illusion or pretending things are different than they are. You can establish trust quickly by being open and authentic, erring on the side of disclosure and not having hidden agendas.

Behavior #4: Right Wrongs. Make restitution instead of just apologizing. The opposite is to deny or justify wrongs because of ego and pride, and the counterfeit is to cover up mistakes. Apologize quickly, take action to make restitution when possible, and demonstrate personal humility to achieve this behavior.

Behavior #5: Show Loyalty. Give credit to others and speak about people as though they are present. The opposite is to take credit or not represent people fairly. The counterfeit is to appear to share credit but then downplay others’ contribution when they are away. To exhibit a trustworthy character, give credit freely, don’t badmouth people behind their backs and don’t disclose others’ private information.

Competence-Based Behaviors

Behavior #6: Deliver Results. This is a way to convert cynics and establish trust in a new relationship. Because it is often difficult to measure results, take time to define results up front. By establishing a track record, making the right things happen, being on time and on budget, and not making excuses for not delivering, you quickly restore lost trust on the competence side.

Behavior #7: Get Better. Continuously improve by learning, growing and renewing yourself. Others will develop confidence in your ability to succeed in a rapidly changing environment. The opposite is entropy and deterioration, while the counterfeit is the eternal student always learning, but never producing. Don’t be afraid to make mistakes but learn from them. Develop formal and informal feedback systems and respond to them.

Behavior #8: Confront Reality. Take the tough issues head-on. This affects speed and cost by facilitating open interaction and fast achievement, and also allowing you to engage the creativity, capability and synergy of others in solving problems. When leaders use the opposite behavior by ignoring problems, they pay a huge tax when people feel they are being dishonest. It is far better to address the real issues and lead courageously in discussions of uncomfortable topics.

Behavior #9: Clarify Expectations. Create shared vision and agreement up front. The opposite is to leave undefined expectations and the counterfeit is to be vague about specifics. Consider that most circumstances encompass three variables — quality, speed and cost but you can only have two. Always discuss and reveal expectations, and never assume they are clear or shared. Renegotiate if necessary, but don’t violate expectations once they have been validated.

Behavior #10: Practice Accountability. Hold yourself and others accountable. Leaders who generate trust do both. The opposite is not to take responsibility, and the counterfeit is to point fingers. Other people respond to accountability — particularly performers. They want to be held accountable. Don’t avoid or shirk responsibility, and be clear on how you’ll communicate progress.

Character & Competence Behaviors

Behavior #11: Listen First. Genuinely understand another person’s thoughts and feelings, before trying to diagnose or advise. The opposite and counterfeit are to speak first and listen last, or not at all, and to pretend to listen while waiting for your own chance to speak. Listening teaches you which behaviors create dividends. Use your eyes and your gut to listen as well as your ears, and don’t presume you know what matters to others.

Behavior #12: Keep Commitments. It is the quickest way to build trust in any relationship. The opposite is to break commitments and the counterfeit is to make vague, unreliable commitments, or never make them in the first place. Some cultures view commitments differently, and understanding the difference is key to getting dividends and avoiding trust taxes. People tend to see family commitments as more flexible than work commitments, but they are just as important. Make keeping all commitments the symbol of your honor.

Behavior #13: Extend Trust. Shift trust from a noun to a verb. While the other Behaviors help you become a more trusted person or manager, this 13th Behavior helps you become a more trusting leader. Extending trust leverages it to create reciprocity. The opposite is withholding trust. The counterfeit is extending false trust by giving people responsibility, but no authority or resources to complete a task. There is also fake trust that seems like trust until you follow-up behind people and micromanage. Based on the situation, extend conditionally to those who are earning your trust, but extend it abundantly to those who have earned it

Oracle Dataguard Standby - recovery hunging

2008-08-26T06:40:00.000-07:00

Problem in Oracle 9.2.0.8 dataguard Standby:

ORA-00600: internal error code, arguments: [3020], [126207919], [1], [38584], [352663], [36], [], []
ORA-10567: Redo is inconsistent with data block (file# 30, block# 378799)
ORA-10564: tablespace INDEX01
ORA-01110: data file 30: '/dev/rsprod_index11'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 82018
MRP0: Background Media Recovery process shutdown

Action Plan:

Standby and production uses raw devices
1) Make IMAGE copy of datafile 30 using rman on PRIMARY
rman target / catalog username/password@rman
RMAN>COPY DATAFILE 30 TO '/oracle/sprod1/arch/rsprod_index11';

2) sftp this image file '/oracle/sprod1/arch/rsprod_index11' to STANDBY server location '/oracle/sprod1/arch/rsprod_index11'

3) On standby, shutdown all instances and Copy '/oracle/sprod1/arch/rsprod_index11' to raw device using DD command
dd if=/oracle/sprod1/arch/sprod_index11 of=/dev/rsprod_index11 bs=4096 seek=1

4) Start recovery.
Note: FOR AIX use BS=4096 Metalink Note:178965.1

In my town - Goa

2008-08-24T00:25:00.000-07:00

The Seven Sins of Deadly Meetings

2008-08-23T07:14:00.000-07:00

Sin #1: People don't take meetings seriously. They arrive late, leave early, and spend most of their time doodling.
Salvation: Adopt Intel's mind-set that meetings are real work.

Sin #2: Meetings are too long. They should accomplish twice as much in half the time.
Salvation: Time is money. Track the cost of your meetings and use computer- enabled simultaneity to make them more productive.

Sin #3: People wander off the topic. Participants spend more time digressing than discussing.
Salvation: Get serious about agendas and store distractions in a "parking lot."

Sin #4: Nothing happens once the meeting ends. People don't convert decisions into action.
Salvation: Convert from "meeting" to "doing" and focus on common documents.

Sin #5: People don't tell the truth. There's plenty of conversation, but not much candor.
Salvation: Embrace anonymity.

Sin #6: Meetings are always missing important information, so they postpone critical decisions.
Salvation: Get data, not just furniture, into meeting rooms.

Sin #7: Meetings never get better. People make the same mistakes.
Salvation: Practice makes perfect. Monitor what works and what doesn't and hold people accountable

Six Thinking Hats - Holding Effective Meetings

2008-08-23T07:11:00.000-07:00

Using the concept of hats, Dr Edward de Bono, author of the book Six Thinking Hats, gives an effective and easy-to-practice method of drastically improving your thinking process, discussions and meetings.

The Basics
1) There are six different imaginary hats that you can put on or take off.
2) Each hat is a different color and represents a different type or mode of thinking.
3) We all wear the same hat (do the same type of thinking) at the same time.
4) When we change hats - we change our thinking.

White Hat
With this thinking hat you focus on the data available. Look at the information you have, and see what you can learn from it. Look for gaps in your knowledge, and either try to fill them or take account of them. This is where you analyze past trends, and try to extrapolate from historical data. White Hat excludes Hunch, Intuition, Judgement based on past experience, Feeling, Impression and Opinion. In White Hat thinking, facts should NOT be used to support or suppress a point of view – they should be neutral.

Green Hat
The Green Hat stands for creativity. This is where you can develop creative solutions to a problem. It is a freewheeling way of thinking, in which there is little criticism of ideas.

Yellow Hat
The Yellow Hat helps you to think positively. It is the optimistic viewpoint that helps you to see all the benefits of the decision and the value in it. Yellow Hat thinking helps you to keep going when everything looks gloomy and difficult.

Black Hat
Becoming the Devil’s Advocate. Using Black Hat thinking, look at all the bad points of the decision. Try to see why it might not work. Black Hat thinking helps to make your plans 'tougher' and more resilient. It can also help you to spot fatal flaws and risks before you embark on a course of action.

Red Hat
'Wearing' the Red Hat, you look at problems using intuition, gut reaction, and emotion. Also try to think how other people will react emotionally. Try to understand the responses of people who do not fully know your reasoning. No need to justify or give reasons for the emotions Useful to do a Red Hat session at the beginning, and then at the end to see whether people now feel differently

Blue Hat
The Blue Hat stands for process control. This is the hat worn by people chairing meetings. When running into difficulties because ideas are running dry, they may direct activity into Green Hat thinking. When contingency plans are needed, they will ask for Black Hat thinking, etc.

Sequencing the Hats
No fixed rules, but can be used effectively to drive efficient meetings. An example sequence could be:
1) Blue Hat: Decide Agenda and Sequence
2) White Hat: State facts
3) Green Hat: Brainstorm
4) Yellow Hat: List Advantages
5) Black Hat: List Pitfalls and Disadvantages
6) Red Hat: See how people feel
7) Blue Hat: Take a final decision and assign Action Items

Facilitator's Role:
1) Define the focus of your thinking
2) Plan the sequence and timing of the thinking
3) Ask for changes in the thinking if needed
4) Handle requests from the group for changes in the thinking
5) Form periodic or final summaries of the thinking for consideration by the team

Participant's Role:
1) Follow the lead of Six Thinking Hats facilitator
2) Stick to the hat (type of thinking) that is in current use
3) Try to work within the time limits
4) Contribute honestly & fully under each of the hats

Rules of the Game!
1) You have to follow the rules of the game!
2) Hats describe direction to be followed, not people.
3) Everyone has to wear the same color hat at any one time – you cannot have different people wearing different hats at the same time!
4) Do not try to change personalities – if you are aggressive, you can continue to be so, as long as your aggression is in the correct direction required at that time!

Let's think!