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;

Optimize UNDO Parameters

When working with UNDO (instead of Rollback) two things are important to consider.
1. The size of the UNDO tablespace
2. The Undo_retnetion parameter
A particular size for the UNDO tablespace can be selected and set the UNDO_RETENTION parameter to an best possible value according to the undo size and the relevant database activity. If you have limited diskspace, you don’t want to allocate more space than necessary to the Undo tablespace.

The following query will help to optimize the UNDO_RETENTION parameter

SELECT d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE (MEGS)",
SUBSTR (e.VALUE, 1, 25) "UNDO RETENTION (Secs)",
ROUND ((d.undo_size / (TO_NUMBER (f.VALUE) * g.undo_block_per_sec))
) "OPTIMAL UNDO RETENTION (Secs)"
FROM (SELECT SUM (a.BYTES) undo_size
FROM v$datafile a, v$tablespace b, dba_tablespaces c
WHERE c.CONTENTS = 'UNDO'
AND c.status = 'ONLINE'
AND b.NAME = c.tablespace_name
AND a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(SELECT MAX (undoblks / ((end_time - begin_time) * 3600 * 24)
) undo_block_per_sec
FROM v$undostat) g
WHERE e.NAME = 'undo_retention' AND f.NAME = 'db_block_size';



If disk space it not an issue, then it would be ideal to choose the UNDO_RETENTION time that is best. (Flashback etc). Assign the suitable size to the UNDO tablespace according to the database activity:

SELECT d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE (MEGS)",
SUBSTR (e.VALUE, 1, 25) "UNDO RETENTION (Secs)",
(TO_NUMBER (e.VALUE) * TO_NUMBER (f.VALUE) * g.undo_block_per_sec
)
/ (1024 * 1024) "NEEDED UNDO SIZE (MEGS)"
FROM (SELECT SUM (a.BYTES) undo_size
FROM v$datafile a, v$tablespace b, dba_tablespaces c
WHERE c.CONTENTS = 'UNDO'
AND c.status = 'ONLINE'
AND b.NAME = c.tablespace_name
AND a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(SELECT MAX (undoblks / ((end_time - begin_time) * 3600 * 24)
) undo_block_per_sec
FROM v$undostat) g
WHERE e.NAME = 'undo_retention' AND f.NAME = 'db_block_size';

Calculate Tablespace Sizes

SELECT df.tablespace_name "Tablespace"
,round((sum(nvl(fs.bytes,0))/ (df.bytes)) * 100)
"%Free"
,round(((df.bytes - sum(nvl(fs.bytes,0)))
/ (df.bytes) ) * 100) "%Used"
,round(sum(nvl(fs.bytes/1024/1024,0))) "Mb Free"
,round(df.bytes/1024/1024
- sum(nvl(fs.bytes/1024/1024,0))) "Mb Used"
FROM dba_free_space fs, dba_data_files df
WHERE fs.file_id(+) = df.file_id
GROUP BY df.tablespace_name, df.file_id, df.bytes,
df.autoextensible
ORDER BY df.file_id;

Calculate the database size

SELECT 'DB Size' "*****"
,round(sum(round(sum(nvl(fs.bytes/1024/1024,0)))) /
sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round
(df.bytes/1024/1024 – sum(nvl(fs.bytes/1024/1024,0))))
* 100, 0) "%FREE"
,round(sum(round(df.bytes/1024/1024 –
sum(nvl(fs.bytes/1024/1024,0)))) / sum(round(sum
(nvl(fs.bytes/1024/1024,0))) + round(df.bytes/1024/
1024 – sum(nvl(fs.bytes/1024/1024,0)))) * 100, 0)
"%USED"
,sum(round(sum(nvl(fs.bytes/1024/1024,0)))) "FREE MB"
,sum(round(df.bytes/1024/1024
– sum(nvl(fs.bytes/1024/1024,0)))) "USED MB"
,sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round
(df.bytes/1024/1024
– sum(nvl(fs.bytes/1024/1024,0)))) "Size"
FROM dba_free_space fs, dba_data_files df
WHERE fs.file_id(+) = df.file_id
GROUP BY df.tablespace_name, df.file_id, df.bytes,
df.autoextensible
ORDER BY df.file_id;

List all constraints

SELECT DECODE (t.constraint_type,
'P', 'Primary',
'R', 'Foreign',
'U', 'Alternate',
'Unknown'
) "Key",
t.table_name "Tab", t.constraint_name "Cons", c.column_name "Col",
c.POSITION "Pos"
FROM user_constraints t, user_cons_columns c
WHERE t.constraint_type IN ('P', 'R', 'U')
AND t.table_name = c.table_name
AND t.constraint_name = c.constraint_name
ORDER BY t.table_name, t.constraint_type, c.POSITION;

One of the issues using ASM is its invisibility to the operating system and moving a file within ASM can be tricky.
Recently I needed to move a control file from one diskgroup to another and he is the solution.

--Create the new diskgroup
ASM>CREATE DISKGROUP CMP_PRE_REDO
EXTERNAL REDUNDANCY
DISK '/dev/rdsk/c6t60060480000287750753474B50354345d0s7',
'/dev/rdsk/c6t60060480000287750753474B50354441d0s7',
'/dev/rdsk/c6t60060480000287750753474B50363136d0s7',
'/dev/rdsk/c6t60060480000287750753474B50363232d0s7',
'/dev/rdsk/c6t60060480000287750753474B50354645d0s7',
'/dev/rdsk/c6t60060480000287750753474B50354632d0s7',
'/dev/rdsk/c6t60060480000287750753474B50354536d0s7',
'/dev/rdsk/c6t60060480000287750753474B50363041d0s7';



SQL>ALTER DATABASE ADD LOGFILE MEMBER '+CMP_PRE_REDO' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '+CMP_PRE_REDO' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '+CMP_PRE_REDO' TO GROUP 3;
ALTER DATABASE ADD LOGFILE MEMBER '+CMP_PRE_REDO' TO GROUP 4;
ALTER DATABASE ADD LOGFILE MEMBER '+CMP_PRE_REDO' TO GROUP 5;
ALTER DATABASE ADD LOGFILE MEMBER '+CMP_PRE_REDO' TO GROUP 6;

SQL>alter system switch logfile

ALTER DATABASE DROP LOGFILE MEMBER '+CMP_PRE_FRA/wpscs02q/onlinelog/group_1.3601.643130119'


export ORACLE_SID=CMPBT01S
restore controlfile to '+CMP_PRE_REDO' from '+CMP_PRE_DATA/CMPBT01S/CONTROLFILE/Current.689.650299077';
alter system set control_files='+cmp_pre_redo/cmpbt01s/controlfile/current.289.650374053' scope=spfile;

+cmp_pre_redo/cmpbt01s/controlfile/current.289.650374053

How much space each user is consuming

SELECT u.NAME Owner,
t.NAME Tablespace,
SUM((seg.blocks * t.BLOCKSIZE)/1024)/1024 MB
FROM SYS.ts$ t,
SYS.user$ u,
SYS.seg$ seg
WHERE seg.user# = u.user#
AND t.ts# = seg.ts#
GROUP BY u.NAME, t.NAME

Resolving Disk Space Issues in Grid Control

After trying to apply templates to a series of databases, I was constantly receiving a message while removing all policies from the database configuration.

“Error: return number of status does not match 1!=0”

(I don’t understand why Oracle can’t provide meaningful messages)

I managed to track down the message, and it’s related to threshold settings within the agent! Ultimately the agent was complaining that I didn’t have enough disk space to complete the job.

I checked on the server and the disk space had grown to 97% full in our agent home, while the threshold for the agent collecting information stops at 96%, there is also a lower threshold that was set to 95%.

This means, if our disk space reaches 97% then grid control agent will stop collecting information, and if we clean up the space, the agent won’t kick in until we have at least 95%.

The solution is to either turn off the check (which is dangerous) or change the thresholds (recommended). Follow these steps:

1. Stop the agent
2. emctl stop agent
3. Check the value of the parameters in /sysman/config/emd.properties:

# The maximum amount (%) of disk space that can be used on the EMD filesystem
# before the following is disabled:
# - Collection of data (upload manager)
# - Logging and tracing

UploadMaxDiskUsedPct=96

# The amount (%) of disk space that can be used on the EMD filesystem
# before the following is re-enabled after being disabled previously:
# - Collection of data (upload manager)
# - Logging and tracing

UploadMaxDiskUsedPctFloor=95

4. Change the relevant percentages
5. Start the agent
6. emctl start agent

Recover From Missing Redo Log Files

The disk that is holding the most up-to-date redo log files experienced a fatal failure, and the database wouldn`t come up any more. It complains about missing redo logs. So, you copy the redo log files from a recent backup; but still the database won`t come up with error messages like ORA-00314: log 1 of thread 1, expected sequence# doesn`t match.

SQL> startup
ORACLE instance started.
Total System Global Area 4294967296 bytes
Fixed Size 1306568 bytes
Variable Size 2534150200 bytes
Database Buffers 1728053248 bytes
Redo Buffers 31457280 bytes
Database mounted.

ORA-00314: log 1 of thread 1, expected sequence# doesn't match
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata_4/RedoLogFiles/log1.dbf'

How to bring up the database with no redo logs?

In production environments, usually archive logging will be turned on. If that's the case, consult the Oracle system administration documentation to restore the lost transactions.

In all other cases it is very unlikely to recover the lost transactions. So the easiest way to bring up the database is to rebuild the control file. The steps to rebuild the control file with new redo log files are as follows:

Start the database up.

SQL> startup <- let it complain about online redo log

Run the following command to dump the control file into a trace file.

SQL> alter database backup controlfile to trace;
Database altered.

Go to udump location and locate the file which contains the necessary SQL text to build the control file. The easiest way is to grep for text like `CREATE CONTROLFILE REUSE DATABASE`. Once you locate the trace file, change the extension from trc to sql and change NORESETLOGS to RESETLOGS. That is, a SQL like:

CREATE CONTROLFILE REUSE DATABASE "XYZ" NORESETLOGS NOARCHIVELOG

should be changed to

CREATE CONTROLFILE REUSE DATABASE "XYZ" RESETLOGS NOARCHIVELOG

Shut down the database

Run the script that you just modified -- it creates a new control file.

Open the database with

alter database open;

You may have to read the alert messages carefully and act accordingly to fix the things up, if the database won`t come up even after the above step.

Finally bring down the database and bring it up as you usually do.

How to Move Control file in Oracle ASM

1. First get the current control file names

SQL>select name from v$controlfile;

+CMP_PRE_DATA/cmpbt02q/control01.ctl.

2. Next we need to create a pfile
create pfile='/app/oracle/initCMPBT02Q.ora' from spfile;

3. shutdown the database but don't mount it
srvctl stop database -d CMPBT02Q
export ORACLE_SID=CMPBT02Q1
sqlplus / as sysdba
startup nomount
exit

4. Use RMAN to create a copy of the control file into the new diskgroup.

RMAN>rman target /restore controlfile to '+CMP_PRE_REDO' from '+CMP_PRE_DATA/cmpbt02q/control03.ctl';

5. Modify the initCMPBT02Q.ora file with the new control file, which can be obtained from the ASM +CMP_PRE_REDO/CMPBT02Q/CONTROLFILE location. Place this name in the pfile.

6. Shutdown the database immediate shutdown immediate
SQL>shutdown immediate;

7. Mount the database with the modified pfile.
SQL>startup pfile='/app/oracle/initCMPBT02Q.ora'

8. Create the spfile
SQL>create spfile='+CMP_PRE_DATA/CMPBT02Q/spfileCMPBT02Q.ora' from pfile='/app/oracle/initCMPBT02Q.ora';

9. Check you have the new control file location.
SQL>select name from v$controlfile
SQL> set wrap off
SQL> select name from v$controlfile;

NAME-------------------------------------------------------
+CMP_PRE_DATA/cmpbt02q/control01.ctl
+CMP_PRE_REDO/cmpbt02q/current.290.650448573
+CMP_PRE_DATA/cmpbt02q/control03.ctl

10. Start database services

HTC Diamond Setup For Telstra WAP

Instructions:
Please follow the set up instructions below.
1. On the handset, tap on the Start button.
2. Tap on Settings.
3. Tap on Connections tab.
4. Tap on Connections icon.
5. Tap on Add a new modem connection under My Work Network.
6. Enter Telstra Internet in the Enter a name for the connection field.
7. Select Cellular Line (GPRS, 3G) in the drop down list under Select a modem.
8. Tap on Next.
9. Enter telstra.wap in the Access point name field.
10. Tap on Next.
11. Enter a username in the User name field or otherwise leave it blank.
12. Enter a password in the Password field or otherwise leave it blank.
13. Tap on Advanced.
14. Select Use server-assigned IP address.
15. Tap on OK.
16. Tap on Finish.
17. Tap on Set up my proxy server under My Work Network.
18. Make sure that This network connects to the Internet and This network uses a proxy server to connect to the Internet are checked.
19. Tap on Advanced.
20. Tap on HTTP.
21. Enter 10.1.1.181 in the Server field.
22. Enter 80 in the Port field.
23. Tap on OK three times.

The handset is now configured for using WAP.