Oracle DBA - Power of knowledge is in sharing

Thursday 3 May 2012

Oracle Application Server Status

11g DIAGNOSTIC_DEST Initialization parameter

Checking the background_dump_dest for alert log files and user_dump_dest for trace files and errors are going to be a past.

I was excited to know about the new oracle11g initialization parameter DIAGNOSTIC_DEST which replaced BACKGROUND_DUMP_DEST, USER_DUMP_DEST and CORE_DUMP_DEST parameters.

All the trace informations are stored in a single location specified using DIAGNOSTIC_DEST. This helps the Oracle 11g database advisors to have a single point of contact for all the information required to auto tune the SQL or database.

If we do not specify a location for this initialization parameter then it will default to the $ORACLE_BASE/diag

Error 45 initializing SQL*PLUS Internal Error

During pre-11g check the sql script execution fails with the "Error 45 Initializing SQL*PLUS" on the sqlplus as below

SQL> spool utlu1112i.log

SQL> @utlu112i.sql

Error 45 initializing SQL*Plus

Internal error

The reason is the path where the 11g script utlu112i.sql script copied to on the 10g database server.

Solution:

Copy the utlu112i.sql script to the $ORACLE_HOME/rdbms/admin directory and execute the script.

SQL> spool utlu1112i.log

SQL> @utlu112i.sql

Wednesday 2 May 2012

Enterprise Manager 11g fails database instance unavailable

In Oracle 11g RAC the enterprise manager dbconsole doesn't start and gives an OC4J not available error.
If we try to deconfig using emca as below we receive an error database instance unavailable. If you face the same issue then here is one of the possible solution

export ORACLE_SID to the instance id not db id for example if your 2 node rac db is orcl then instance will be orcl1 & orcl2.

Set the ORACLE_SID to orcl2 and export the oracle sid

Go to oracle_home/bin directory and check the emca and emctl files and update if required for the ORACLE_HOME & ORACLE_SID.

And then execute the below command to remove the EM repository

[oracle@home bin]$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at May 2, 2012 11:17:49 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl1
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: Y
May 2, 2012 11:18:03 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /opt/oracle/cfgtoollogs/emca/racdb/emca_2012_05_02_11_17_49.log.
May 2, 2012 11:18:03 AM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
May 2, 2012 11:18:06 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...

And after dropping recreate the repository.

Tuesday 3 April 2012

How to write a blob pdf data from the Oracle database to a pdf file

How to write a blob pdf data from the Oracle database to a pdf file

create directory orcl_dir as '/mnt/oracle/orcl_dir'

--In my example I am passing two variables
CREATE OR REPLACE PROCEDURE BLOBToFILESample (typeidin in number,varnamein IN VARCHAR2) IS
type_id number(11);
var_name varchar2(40);
v_blob BLOB;
blob_length INTEGER;
out_file UTL_FILE.FILE_TYPE;
v_buffer RAW(32767);
chunk_size BINARY_INTEGER := 32767;
blob_position INTEGER := 1;

BEGIN

-- Retrieve the BLOB for reading
SELECT myblobcolumn INTO v_blob FROM mytablename WHERE mycol1=typeidin and mycol2 = varnamein;

-- Retrieve the SIZE of the BLOB
blob_length:=DBMS_LOB.GETLENGTH(v_blob);

--ORCL_DIR is the directory which i have created on my database.
out_file := UTL_FILE.FOPEN ('ORCL_DIR', varnamein, 'wb', chunk_size);

-- Write the BLOB to file in chunks
WHILE blob_position <= blob_length LOOP
IF blob_position + chunk_size - 1 > blob_length THEN
chunk_size := blob_length - blob_position + 1;
END IF;
DBMS_LOB.READ(v_blob, chunk_size, blob_position, v_buffer);
UTL_FILE.PUT_RAW(out_file, v_buffer, TRUE);
blob_position := blob_position + chunk_size;
END LOOP;

-- Close the file handle
UTL_FILE.FCLOSE (out_file);
END;
/

The above code will save the file in the directory orcl_dir.

Some of the error I have encountered is when passing the parameters to the procedure do not give the same name as the column name. If you specify then you may encounter ora-01422 as below

ORA-01422: exact fetch returns more than requested number of rows

Tuesday 27 March 2012

Voting Disk and OCR backups in Oracle 11g R2

In Oracle 11g R2, the OCR and voting disks are backed up automatically into a single file. To check the backup of the OCR and voting disks

# $GRID_HOME/bin/ocrconfig -showbackup

Where GRID_HOME is your Grid installation home directory. In my example is

# /u01/app/11.2.0/grid/bin/ocrconfig -showbackup

Wednesday 11 January 2012

How to move ocr and voting disks to a new directory in OCFS

How to move ocr and voting disks to a new directory in OCFS?

Moving OCR (Oracle Cluster Registry)

1. Login as root user and check for a recent backup of the ocr file.

#ocrconfig –showbackup

2. If the backup does not exists then take a backup

#ocrconfig –export –s online

3. Check whether the crs and its processes are running

#crsctl check crs

4. Check the location of the crs files

#ocrcheck

5. To move the ocr file to a new location - /u01/oradata/racdb

#ocrconfig –replace ocr /u01/oradata/racdb/OCRFile

6. Check whether the crs is pointed to new location

#ocrcheck

#cat /etc/oracle/ocr.loc

#Device/file /u02/oradata/racdb/OCRFile getting replaced by device /u01/oradata/racdb

ocrconfig_loc=/u01/oradata/racdb/OCRFile

ocrmirrorconfig_loc=/u02/oradata/racdb/OCRFile_mirror

7. Check the privilege and permissions of OCR file. It should be owned by root and group dba/oinstall. If not change as below

#chown root:oinstall /u01/oradata/racdb/OCRFile

Moving voting disks to a new location

1. Login as root

2. Stop the clusterware

#crsctl stop crs

3. Check the voting disks

#crsctl query crs votedisk

4. Take a backup of the voting disk

#dd if=voting_disk_name of=backup_file_name

5. Add the voting disk to a new location

#crsctl add css votedisk /u01/oradata/racdb/CSSFile –force

6. Delete the old voting disk

#crsctl delete css votedisk /u02/oradata/racdb/CSSFile –force

7. Check the voting disk file permission

The css file should be owned by oracle with oinstall / dba group depends on your primary group

8. Start the cluster

#crsctl start crs

9. Check the voting disk new location

#crsctl query crs votedisk

Tuesday 10 January 2012

How to Stop a cluster in Oracle RAC

To stop a cluster in Oracle RAC, As a root user, execute the command

# /bin/crsctl stop crs

If your crs home is /opt/oracle/crs then

#/opt/oracle/crs/bin/crsctl stop crs