Oracle SPFILE and PFILE

Oracle provides two different parameter files, PFILE and SPFILE.

PFILE is a text based file and in order to add/modify any database parameters, need to edit the INIT.ORA file using “vi” in unix or notepad in windows. To apply new or modified database parameters changes, database restart is required.

SPFILE is a binary file and introduced in Oracle 9i. SPFILE simplifies administration, maintaining parameter settings consistent and Server parameter file is a binary file let you make persistent changes to individual parameters. Use the CREATE SPFILE statement to create a Server Parameter file from PFILE with SYSDBA/SYSOPER privilege.

By default PFILE or SPFILE default location is “$ORACLE_HOME/dbs” for UNIX and LINUX, %ORACLE_HOME%\database for Windows. In case of RAC, SPFILE located on the shared storage.

You can change the SPFILE parameters using Enterprise manager or ALTER SYSTEM SET ‘parameter’ statement with SCOPE clause. The SCOPE clause has three values MEMORY, SPFILE and BOTH.

SPFILE: The change is applied in the server parameter file only and is effective at the next startup.
For Ex: - SQL> ALTER SYSTEM SET SGA_MAX_SIZE=1024m SCOPE=spfile;

Memory: The change is applied in memory only and the effect is immediate.
For Ex:- SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=50 SCOPE=MEMORY;

BOTH: The change is applied in both the server parameter file and memory. For dynamic parameters the effect is immediate.
Ex: - SQL> ALTER SYSTEM SET SGA_TARGET=1024m SCOPE=BOTH;

For Static parameters the MEMROY, BOTH specifications are not allowed. Only SPFILE is allowed.

If you do not specify the SCOPE clause then the default is BOTH. For Dynamic parameters you can specify DEFERRED keyword and the specified change is effective only for future sessions.

During Database startup Oracle searches for initialization parameter file under $ORACLE_HOME/dbs in UNIX and $ORACLE_HOME/database on Windows in following order.

spfileSID.ora
spfile.ora
initSID.ora
init.ora

Backup/Restore parameter files(SPFILE and PFILE):
Using Recovery Manager(RMAN), SPFILE can be backed up with database control file by setting
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
SPFILE can be restored using following RMAN command.
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

PFILE cannot be backed-up using RMAN, backup and restore can be done using O/S copy command.

Common Errors and solutions in modifying SPFILE:
1. SQL>alter system SET LOG_ARCHIVE_DEST='/oradata/TESTDB' scope=both;
alter system SET LOG_ARCHIVE_DEST='/oradata/ TESTDB' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST

Solution: OCATION keyword is required.
SQL> alter system set log_archive_dest_1='location=/oradata/ TESTDB' scope=both;
System altered.

2. SQL> alter system set log_archive_dest_1='location=/oradta/ TESTDB' scope=both;
alter system set log_archive_dest_1='location=/oradata/ TESTDB' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
Linux-x86_64 Error: 2: No such file or directory

Solution: LOG_ARCHIVE_DEST_1 is incorrect or does not exist. Prove correct path.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Send EMAIL using UTL_MAIL in Oracle 10g

The UTL_MAIL package is introduced in Oracle 10g and it is easier to use when compared to UTL_SMTP. In order to use Oracle UTL_MAIL package you need to set a new init.ora parameter “SMTP_OUT_SERVER”, set to your outgoing mailserver.

Follow the simple steps to send an email using UTL_MAIL package

Step 1: Install UTL_MAIL package
To install the UTL_MAIL package, run the below files as user "SYS"
$ORACLE_HOME/rdbms/admin/utlmail.sql
$ORACLE_HOME/rdbms/admin/prvtmail.plb

Step 2: Grant permissions
Grants the execute permission on UTL_MAIL privilege to PUBLIC or the user which will use the package. Run the beow command as user “SYS”
SQL> GRANT EXECUTE ON UTL_MAIL TO PUBLIC;
-or-
SQL> GRANT EXECUTE ON UTL_MAIL TO ;

Step 3: Set SMTP_OUT_SERVER parameter
SQL> ALTER SYSTEM SET smtp_out_server=’smtp.domain.com’ SCOPE=both;

Step 4: Create procedure to send email
CREATE OR REPLACE PROCEDURE test_email AS
BEGIN
UTL_MAIL.SEND(sender => 'xxx@oracleracexpert.com',
recipients => 'xxx@oracleracexpert.com',
cc => 'xxx@oracleracexpert.com',
bcc => 'xxx@oracleracexpert.com',
subject => 'Test Mail',
message => 'Hi, This is just a test mail');
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'The following error has occured:' ||sqlerrm);
END;

SQL> exec test_email;

Step 5: Send email using UTL_MAIL with attachments
You must set UTL_FILE_DIR to a directory, where the attachment files exists

CREATE OR REPLACE PROCEDURE test_email_attach AS
BEGIN
UTL_MAIL.SEND_ATTACH_VARCHAR2(sender => 'xxx@oracleracexpert.com',
recipients => 'xxx@oracleracexpert.com',
cc => 'xxx@oracleracexpert.com',
bcc => 'xxx@oracleracexpert.com',
subject => 'Test Mail',
message => 'Hi, This is just a test mail'
attachment => ‘text’
att_filename => ‘test_attach.txt’);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'The following error has occured: '
sqlerrm);
END;

Please note that with att_inline you can specify, whether the attachment is viewable inline with the message body or not.

You can also use the below parameters
attachment - A text attachment.
priority - The message priority, the default is NULL.
mime_type - The mime type of the message, default is ‘text/plain; charset=us-ascii‘.
att_inline - Specifies whether the attachment is viewable inline with the message body, default is TRUE.
att_mime_type - The mime type of the attachment, default is ‘text/plain; charset=us-ascii‘.
att_filename - The string specifying a filename containing the attachment, default is NULL.

You can also send email with Multiple attachements using UTL_SMTP, follow the steps.

Step 1: Creating the Email Attachments table
SQL> CREATE TABLE email_attachments(id_pk INTEGER PRIMARY KEY,fname VARCHAR2(50),image BLOB);

Table created.

Step 2: Use below procedure to load attachments into the table
CREATE OR REPLACE PROCEDURE load_file(pi_id IN INTEGER, pfname IN VARCHAR2) IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file := bfilename('TEMP', pfname);

INSERT INTO email_attachments (id_pk,fname,image)
VALUES (pi_id,pfname, EMPTY_BLOB())
RETURNING image INTO dst_file;

DBMS_LOB.OPEN(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
DBMS_LOB.LOADFROMFILE(dst_file, src_file, lgh_file);
dbms_lob.close(src_file);
COMMIT;
END load_file;
/

Step 3: Loading some attachments using the procedure
SQL> EXECUTE load_file(1,'attachment1.zip');

Step 4: Query email_attachments table to check the attachments
SQL> SELECT id_pk,fname,DBMS_LOB.getlength(image) image_length FROM email_attachments;

Step 5: Use below procedure to send emails with multiple attachemnts

DECLARE
/*LOB operation related varriables */
l_buffer RAW(54);
l_amount BINARY_INTEGER := 54;
l_pos INTEGER := 1;
l_blob BLOB := EMPTY_BLOB;
l_blob_len INTEGER;
v_amount INTEGER;
v_fname VARCHAR2(50);

/* This cursor will pick the images for attachment in the email_attachments table. */
CURSOR img_cur IS SELECT fname,image FROM email_attachments;

/*UTL_SMTP related varriavles. */
v_connection_handle UTL_SMTP.CONNECTION;
v_from_email_address VARCHAR2(30) := 'xxxxxxx@xxx.com';
v_to_email_address VARCHAR2(30) := 'xxxxxxx@xxxxx.com';
v_smtp_host VARCHAR2(30) := 'xxx.xxx.xxx.xxx'; --Mail server
v_subject VARCHAR2(30) := 'Your Test Mail';
l_message VARCHAR2(200) := 'This is test mail using UTL_SMTP';

/* This send_header procedure is written in the documentation */
PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(v_connection_handle,pi_name || ': ' || pi_header || UTL_TCP.CRLF);
END;

BEGIN

/*UTL_SMTP related coding. */
v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);
UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
UTL_SMTP.OPEN_DATA(v_connection_handle);
send_header('From', '"Sender" <'>');
send_header('To', '"Recipient" <'>');
send_header('Subject', v_subject);

--Mail header
UTL_SMTP.WRITE_DATA(v_connection_handle,
'MIME-Version: 1.0' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Type: multipart/mixed; ' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||
UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

-- Mail Body
UTL_SMTP.WRITE_DATA(v_connection_handle,
'--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Type: text/plain;' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
' charset=US-ASCII' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

/* Opening the cursor to loop through the attachments*/
OPEN img_cur;
LOOP
FETCH img_cur INTO v_fname,l_blob;
EXIT WHEN img_cur%NOTFOUND;

-- Mail Attachment
UTL_SMTP.WRITE_DATA(v_connection_handle,
'--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Type: application/octet-stream' ||
UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Disposition: attachment; ' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
' filename="' || v_fname || '"' || --My filename
UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

/* Re-initializing the varriables. This is very important*/
l_buffer := NULL;
l_pos :=1;
l_amount:=54;
/* Writing the BLOL in chunks */
l_blob_len := DBMS_LOB.getlength(l_blob);
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
UTL_SMTP.write_raw_data(v_connection_handle,
UTL_ENCODE.BASE64_ENCODE(l_buffer));
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
l_buffer := NULL;
l_pos := l_pos + l_amount;
END LOOP;
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
l_blob:=EMPTY_BLOB;
END loop; --End cursor loop.

CLOSE img_cur; --Close the cursor.

-- Close Email
UTL_SMTP.WRITE_DATA(v_connection_handle,
'--' || 'SAUBHIK.SECBOUND' || '--' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);

UTL_SMTP.CLOSE_DATA(v_connection_handle);
UTL_SMTP.QUIT(v_connection_handle);
DBMS_LOB.FREETEMPORARY(l_blob);

EXCEPTION
WHEN OTHERS THEN
UTL_SMTP.QUIT(v_connection_handle);
DBMS_LOB.FREETEMPORARY(l_blob);
RAISE;
END;
/

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

How to restore a database to Point in Time using RMAN

You can recover whole database to a specific SCN, time or log sequence number using RMAN, this is called incomplete recovery or point-in-time Recovery (DBPITR).

You must restore all data files from backups create prior to the time to which you want to recovery and you must open database with RESETLOGS option when completes. Please note that RESETLOGS operation creates a new incarnation of the database.

Usually incomplete recovery will be performed under following situations:
1. Corrupt or destroy of some or all online redo logs due to media failure
2. User error causes data loss, for ex accidental drop of a table
3. Archive log missing and you cannot able to perform incomplete recovery
4. Loss of current control file and must use a backup control file to open database.



Follow the steps to recover the database until a specified SCN, time or log sequence

Step 1: Shutdown and startup mount
SQL> SHUTDOWN IMMEDAITE;
SQL> STARTUP MOUNT;

Step 2: Determine the SCN, time or log sequence that you want to recovery the
SCN – you can get the SCN from alert.log file
Sequence – v$log_history

3. Perform the incomplete recovery
If specifying a time, then set NLS_LANG and NLS_DATE_FORMAT environment variables.

RUN
{
SET UNTIL TIME 'Aug 10 2009 11:00:00';
# SET UNTIL SCN 100; # alternatively, specify SCN
# SET UNTIL SEQUENCE 123; # alternatively, specify log seq
RESTORE DATABASE;
RECOVER DATABASE;
}

4. Open database with resetlogs
SQL> ALTER DATABASE OPEN RESETLOGS;

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Oracle ODBC, OCI, OCCI Drivers, Downloads and Documentation

Oracle ODBC(Open Database Connectivity):
The Oracle ODBC Driver provides access to Oracle databases for applications written using the ODBC interface. With ODBC you can access the Oracle database using .NET, applications from any .NET programming language.

Download Oracle ODBC Driver
http://www.oracle.com/technology/software/tech/windows/odbc/index.html

Oracle ODBC Documentation
http://www.oracle.com/technology/docs/tech/windows/odbc/index.html

Please see the Oracle ODBC Driver discussion forum for any questions/issues.
http://forums.oracle.com/forums/forum.jsp?forum=145

Oracle Call Interface(OCI):
The Oracle Call Interfaces (OCI) is a set of low-level APIs (Application Programming Interface Calls) used to interact with the Oracle Database. It allows one to use operations like logon, execute, parse, fetch, etc.

Download Oracle Call Interface (OCI)
http://www.oracle.com/technology/tech/oci/instantclient/index.html

Please see the below link for OCI documentation
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14250/toc.htm

Please see the OCI (Oracle Call Interface) Discussion forum for any questions/issues
http://forums.oracle.com/forums/forum.jspa?forumID=67

Oracle C++ Call Interface:
Oracle C++ Call Interface (OCCI) is a high-performance and comprehensive API to access the Oracle database and used for client-server, middle-tier, and complex object modeling applications.

Download OCCI for Linux/Windows
http://www.oracle.com/technology/tech/oci/occi/occidownloads.html

Please see the below link for documentation
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28390/toc.htm

Please see the C++ Call Interface (OCCI) Discussion forum for any questions/issues.
http://forums.oracle.com/forums/forum.jspa?forumID=168

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/