Steps to create ASM disk group

Steps to Re-Create ASM Diskgroups
In the event you cannot mount your ASM disk groups, you will be unable to start any databases using those disk groups. Here is a possible error reported when mounting ASM disk groups:

SQL> startup mount

ORA-15032: not all alterations performed
ORA-15063: diskgroup "" lacks quorum of 2 PST disks; 0 found

This error may occur if:

a) ASM disk(s) is not visible on the operating system.
b) asm_diskstring parameter is not set correctly on ASM instance(s)
c) ASM metadata in disk is overwritten or corrupted

If you have seen this error or another error indicating ASM metadata corruption and have verified that the disk(s) is visable with correct permissions on the operating system and that the asm_diskstring parameter is set correctly, your ASM metadata may be corrupted. If this is the case, you may need to re-create your ASM instance(s) and disk group(s). The steps are:

1. Ensure that you have a prior RMAN backup of all databases using ASM
2. Shut down your ASM instance(s)
3. Use dd to clear the metadata from ASM disks
4. Re-create your ASM disk group(s)
5. Restore databases

Step 1: Ensure that you have a prior RMAN backup of all databases using ASM
The only way you can recover from ASM metadata corruption is to have a prior RMAN backup of the database in an area that would not be affected by an ASM instance outage. As part of your recovery strategy, you should consider integrating tape or other tertiary storage to safeguard your backups.

Example of RMAN backup:

1. Connect RMAN to the target database for backup

rman nocatalog target /

2. Now Backup your Database, Archive logs and Control files. Example:

RMAN> backup device type disk format '/u03/backup/%U' database plus archivelog;
RMAN> backup device type disk format '/u03/backup/ctrlf_%U' current controlfile;

3. Manually make copies of your spfiles. Example:

CREATE PFILE='/u03/app/oracle/product/10.1.0/dbs/init.ora'
FROM SPFILE='/+DATA/V10FJ/spfile.ora';

If you do not have a good backup of all databases (datafiles, controlfiles,
redo logs, archive logs), DO NOT CONTINUE BEYOND STEP 1!

Step 2: Shutdown your ASM instance(s)
Stop your database instances and ASM instances with sqlplus or srvctl (RAC)

SQLPLUS Example:

setenv ORACLE_SID +ASM
sqlplus '/ as sysdba'
SQL> shutdown immediate

setenv ORACLE_SID DBSCOTT
sqlplus '/ as sysdba'
SQL> shutdown immediate

SRVCTL (RAC) Example:

srvctl stop asm -n
srvctl stop asm -n
srvctl stop database -d

Step3: Use DD to clear the metadata from ASM disks
All ASM metadata must be cleared before attempting to re-create ASM instances and diskgroups. Example Command:


dd if=/dev/zero of=/dev/rdsk/c1t4d0s4 bs=8192 count=12800

Step 4: Re-create your ASM disk group(s)
Set your ORACLE_SID to your ASM instance and create a new diskgroup. Example:

setenv ORACLE_SID +ASM
sqlplus '/ as sysdba'
SQL> startup nomount
SQL> create diskgroup data disk '/dev/rdsk/c1t4d0s4';
SQL> shutdown immediate
SQL> startup mount

Step 5: Restore database
1. Start instance using the local copy of your pfile from step 1.

setenv ORACLE_SID DBSCOTT
sqlplus '/ as sysdba'
SQL> startup nomount pfile=init.ora

2. Use RMAN to restore the controlfiles and database. Example:

rman target /
RMAN> restore controlfile from '/u03/backup/ctrlf_'; -- where is the unique string generated by %U.
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;

3. Connect to the ASM instance and get the controlfile name. Example:

setenv ORACLE_SID +ASM
sqlplus '/ as sysdba'
SQL> select name, alias_directory from v$asm_alias;

Look for the controlfile name under the CONTROLFILE directory eg: Current.256.1

4. Edit the init.ora and change the control_files parameter to point to
the one identified from the ASM v$asm_alias view.

5. Re-create the spfile. Example:

SQL> create spfile='+DATA/V10FJ/spfileV10FJ.ora'
from pfile='/u03/app/oracle/product/10.1.0/dbs/pfile.out';

6. Shutdown and restart the instance to use the newly created spfile.

7. Repeat the "STEP 5" section for additional databases.

Kill users script

BEGIN
FOR s IN
(
SELECT SID, serial# FROM v$session WHERE program LIKE 'java@itrapdop01%' OR program LIKE 'java@itrapdop02%' AND status = 'ACTIVE'
)
LOOP

EXECUTE IMMEDIATE 'alter system kill session ''' || TO_CHAR(s.SID) || ', ' || TO_CHAR(s.serial#) || ''' IMMEDIATE';
END LOOP;

EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' - ' || SQLERRM);
END;

Oracle RAC 10gR2 OCR and Voting Information

Change the Voting and OCR configuration in RAC 10gR2

The voting disk’s configuration can be changed dynamically

To add a new voting disk
5035>crsctl add css votedisk

To delete a voting disk
5035>crsctl delete css votedisk

If the clusterware is down on all nodes, you will need to use the force option –force.
5035>crsctl add css votedisk -force
5035>crsctl delete css votedisk -force

To backup and recover voting disks Oracle recommend to use symbolic links

To backup the voting disk you need to use the Unix dd command. For more information on the dd command, consult the man pages.
Typically, the voting disk should be backed up
* After an Oracle Clusterware installation
* After a new node has been added or deleted from the cluster
* The backup can be performed on-line.

5035>crsctl query css votedisk
5035>dd if=[voting disk path] of=[output file] bs=4k

You will need to recover voting disks by restoring the first one using the dd command, and then multiplex it if necessary.
If you don’t have a valid backup of your voting disk, you will need to reinstall Oracle Clusterware

Automatic OCR backups
The OCR is crucial to Oracle clusterware
OCR is automatically backed up physically
• Every four hours: CRS keeps the last three copies
• At the end of every day: CRS keeps the last three copies.
• At the end of every week: CRS keeps the last two copies.

The default location of the backups

5035>ocrconfig –backuploc /shared/bak

To manually backup the OCR perform the following

5035>ocrconfig –export [file name]

Backups should always be taken prior to making any changes.

Steps to recover the OCR using physical backups
1. Locate the physical backup
2. 5035>ocrconfig –showbackup
3. Check the contents
4. 5035>ocrdump –backupfile [file_name]
5. Stop clusterware on all nodes
6. 5035>crsctl stop crs
7. Restore the physical backup of the OCR
8. 5035>ocrconfig –restore [CRS_HOME]/data/crs/backup.ocr
9. Restart clusterware on all nodes
10. 5035>crsctl start crs
11. Check the OCR
12. 5035>cluvfy comp ocr –n all

To recover the OCR using logical backups perform the following steps
1. Locate the logical backup using the OCR export
2. Stop clusterware on all nodes
3. 5035>crsctl stop crs
4. Restore the logical OCR backup
5. 5035>ocrconfig –import /shared/export/ocrbackup.dmp
6. Restart the clusterware on all nodes
7. 5035>crsctl start crs

Repair and OCR configuration
1. Stop clusterware on second node
2. 5035>crsctl stop crs
3. Add OCR mirror from the first node
4. 5035>ocrconfig –replace ocrmirror /OCRMirror
5. Repair the OCR mirror location on the second node
6. 5035>ocrconfig –repair ocrmirror /OCRMirror
7. Start the clusterware on the second node
8. 5035>crsctl start crs


Some things to remember when working with OCR
• If you are using raw devices to store OCR files, make sure they exist before you use the add or replace operations
• You must issue the add, replace and remove commands as the root user
• While adding or replacing OCR files, the mirror needs to be online
• If you remove a primary OCR the mirror becomes primary
• NEVER remove the last remaining OCR file

Kill Users killing Portal

select 'alter system kill session ' || CHR(39) || sid || ',' || serial# || CHR(39) || ' immediate;' from v$session where program like 'java@itrapdop01%' or program like 'java@itrapdop02%' and status ='ACTIVE'

See what objects are pinned

SELECT SUBSTR (owner, 1, 10) owner, SUBSTR (TYPE, 1, 12) TYPE,
SUBSTR (NAME, 1, 20) NAME, executions, sharable_mem mem_used,
SUBSTR (kept || ' ', 1, 4) "Kept?"
FROM v$db_object_cache
WHERE TYPE IN ('TRIGGER', 'PROCEDURE', 'PACKAGE BODY', 'PACKAGE')
and owner <> 'SYS'
ORDER BY owner, executions DESC;