Undelivered Mail, Error Code List

2xx Request ok. Go ahead.
220 Mail service is running (ESMTP ready).
221 2.0.0 Closing connection (Mail received successfully, Goodbye).
250 2.0.0 Message accepted for delivery
250 2.1.0 Sender e-mail address ok.
250 2.1.5 Recipient e-mail address ok.
3xx Request ok, but more input is needed to complete the action successfully.
354 Enter mail, end with CRLF.CRLF
4xx Temporary error, try again.
421 4.2.1 The service is not available and the connection will be closed.
441 4.4.1 Cannot connect to remote host (host has DNS entry, but can’t be connected to).
450 4.5.0 Mailbox temporarily unavailable.
451 4.1.8 Sender e-mail address domain does not resolve temporarily.
451 4.7.1 Greylisting in action, please come back later.
454 4.7.0 TLS not available due to local problem. Please try again later.
5xx Permanent error. Don’t try again.
500 5.5.1 Syntax error, command unrecognized.
503 5.5.0 Sender e-mail address already specified.
503 5.5.1 Need MAIL before RCPT.
544 5.4.4 Host not found (not in DNS).
550 5.1.1 Mailbox does not exist.
550 5.1.3 Bad e-mail address syntax.
550 5.7.0 Your IP address is in a blacklist.
553 5.1.8 Sender e-mail address domain does not exist.
554 5.7.1 Relaying denied

Write Data in External File from ORACLE Procedure

Create user mytest identified by mytest;

Grant dba to mytest;

CREATE TABLE MYTEST.TABLE1

(

  NUM1  INTEGER

)

Insert some records in table1

Create a file a.txt at c:\test

CREATE OR REPLACE DIRECTORY MYDIR AS 'c:\test'

GRANT WRITE ON DIRECTORY MYDIR TO DBA

CREATE OR REPLACE procedure MYTEST.myexport as

 FILEID UTL_FILE.FILE_TYPE;

    LINE_BUFF VARCHAR2(1000);

BEGIN

    FILEID:= UTL_FILE.FOPEN ('MYDIR', 'a.txt', 'W');

    FOR emprec IN (SELECT * FROM table1) LOOP

        LINE_BUFF :=TO_CHAR (emprec.num1);

        UTL_FILE.PUT_LINE ( FILEID ,LINE_BUFF);

    END LOOP;

END;

/

Backup & Recovery using RMAN

Objective:  Clone Database from Machine 1 to Machine 2

Environment: Oracle 10g

O/s: Windows XP

Online Backup from Machine 1

1.    Create new database MRBD using Database configuration assistant

o   Default password is MRBD

2.    Login in SQLPLUS as sys user

o   Connect sys/mrbd@mrbd as sysdba

3.    Create folder D:\oradata

4.    Create New tablespace

o   CREATE TABLESPACE tbs1 DATAFILE 'D:\oradata\tbs1.dbf' SIZE 50M

5.    Create New user named ituser

o   create user ituser identified by ituser default tablespace tbs1 ;

6.    GRANT rights to user

o   grant dba to ituser;

7.    Creating table and insert records.

o   connect ituser/ituser;

o   create table emp (empid number,empname varchar2(100)) tablespace tbs1 ;

o   insert into emp values(1,'koko');

o   insert into emp values(2,'momo');

o   insert into emp values(3,'nono');

o   commit;

8.    Create backup folder as d:\bupnew

9.    Go to RMAN

o   Connect sys/mrbd@mrbd

10. Create backup

run {  

allocate channel ch1 type Disk maxpiecesize = 1900M FORMAT 'd:\bupnew\data_%t_set%s_piece%p_dbid%I.rman';

backup as compressed backupset

incremental level 0

tag mrbdbup_1

filesperset 1

(database);

backup archivelog all delete all input FORMAT       'd:\bupnew\arch_%t_set%s_piece%p_dbid%I.rman';

backup current controlfile format 'd:\bupnew\ctl_%t_dbid%I.rman';

}

11. Backup command will creates files

12.   Get DBID

o   Connect sys/mrbd@mrbd as sysdba

o   Select dbid from v$database

Restore Backup on Machine 2

1.    Create new database MRBD using Database configuration assistant

o   Default password is MRBD

2.    Copy folder bupnew from machine 1 to Machine 2

o   Backup folder location must be same.\

3.    Create folder D:\oradata

4.    Login in SQLPLUS as sys user

o   Connect sys/mrbd@mrbd as sysdba

5.    Get database in no mount state by running following command

o   Shutdown immediate

o   Startup nomount

6.    Connect to RMAN

o   Set dbid  1714560268

o   Connect target sys/mrbd@mrbd

7.    Restore control file

o   restore controlfile from ‘D:\bupnew\CTL_819202842_DBID1714560268.RMAN’

8.    Mount the database from sqlplus

o   alter database mount;

9.    Register backup  from RMAN

·         run {

    catalog backuppiece 'D:\bupnew\CTL_819202842_DBID1714560268.RMAN';

}

10. Validate files from RMAN

·           crosscheck archivelog all;

·           crosscheck backup;

11. Get datafiles name from SQLPLUS

·         select file#,name from v$datafile;

12. Run restore command from RMAN

run

{

   set newname for datafile  1 to

 "c:\ORACLE\PRODUCT\10.2.0\ORADATA\MRBD\SYSTEM01.DBF";

   set newname for datafile  2 to

 "c:\ORACLE\PRODUCT\10.2.0\ORADATA\MRBD\UNDOTBS01.DBF";

   set newname for datafile  3 to

 "c:\ORACLE\PRODUCT\10.2.0\ORADATA\MRBD\SYSAUX01.DBF";

   set newname for datafile  4 to

 "c:\ORACLE\PRODUCT\10.2.0\ORADATA\MRBD\USERS01.DBF";

   set newname for datafile  5 to

 "D:\ORADATA\TBS1.DBF";

 RESTORE DATABASE;

    SWITCH DATAFILE ALL;

 }

13. Change all redo log files path in control file (Refer redo log files at C:\oracle\product\10.2.0\oradata\mrbd)

·         alter database rename file 'D:\ORACLE10G\PRODUCT\10.2.0\ORADATA\MRBD\REDO01.LOG' to 'c:\ORACLE\PRODUCT\10.2.0\ORADATA\MRBD\REDO01.LOG';

·         alter database rename file 'D:\ORACLE10G\PRODUCT\10.2.0\ORADATA\MRBD\REDO02.LOG' to 'c:\ORACLE\PRODUCT\10.2.0\ORADATA\MRBD\REDO02.LOG';

·         alter database rename file 'D:\ORACLE10G\PRODUCT\10.2.0\ORADATA\MRBD\REDO03.LOG' to 'c:\ORACLE\PRODUCT\10.2.0\ORADATA\MRBD\REDO03.LOG';

14. recover database from rman

·         RECOVER DATABASE;

·         It shows following exception

o   If it give ORA-19698 then

o   Shutdown database from sqlplus

o   Copy all redo log files to C:\oracle\product\10.2.0\oradata\mrbd from machine 1

o   Startup mount

o   Recover database

15. Open database from sqlplus

·         alter database open resetlogs;

16. Validate the restore using sqlplus

·         Conn ituser/ituser@mrbd

·         Select count(*) from emp  //it should be greater than 0

Install software from RPM packages in Linux

While Installing Oracle 9i on Linux I found some library with old version were required So I did the following

1. Check existing and required library version
2. Copy required package from Linux installation CD
3. Install Package using RPM command
4. refer http://www.tuxfiles.org/linuxhelp/rpminstall.html for more detail