Wednesday, October 30, 2013

ORA-02437 cannot validate

Cause :- This error occur when tries to create a unique index for the unique or primary key index but failed to create unique index.

Solution :-
1. Create an unique index on field.
2. Create NOVALIDATE Primary key.
3. Enable NOVALIDATE Primary Key.
 
CREATE INDEX IND_SY_MENU_USER ON SY_MENU_USER(USER_CODE, CHAILD_ID)

ALTER TABLE SY_MENU_USER
ADD CONSTRAINT PK_SY_MENU_USER_COMP PRIMARY KEY(USER_CODE, CHAILD_ID) NOVALIDATE ;

ALTER TABLE SY_MENU_USER ENABLE NOVALIDATE PRIMARY KEY

Monday, October 28, 2013

Export Import Oracle Dump File Using expde/impdp With Scheduler

Export Import Dump File 

Step 1:- Create Shell File Name exportimport.sh In (/u01/Script )
Step 2:- chmod u+x /u01/Script/exportimport.sh
Step 3:-


# 1. Set Your Environment Veritable.

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=micrdb
export PATH=$PATH:$ORACLE_HOME/bin

# 2. Declare and initialize value or date time.

SET_DATE=`date +%d%m%Y`
export CDATE

DMP_FILE=backup_$SET_DATE.dmp
LOG_FILE=backup_$SET_DATE.log

export DMP_FILE LOG_FILE

# 3. Export Starting



'/u01/app/oracle/product/11.2.0/db_1/bin/expdp' system/system@micrdb schemas=micr,beftn directory=dmp_bkp dumpfile=$DMP_FILE logfile=EXP$LOG_FILE

# 4. Moving File To Backup Folder.

mv /u01/DMP/$DMP_FILE /u01/DUMP_BACKUP/$DMP_FILE

mv /u01/DMP/$LOG_FILE /u01/DUMP_BACKUP/$LOG_FILE

# 5. Connect To Backup Server Drop, Create User And Given Permission.

su - oracle<<EOO

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=bkpdb
export PATH=$PATH:$ORACLE_HOME/bin

sqlplus /nolog << EOF
CONNECT system/system@bkpdb
SPOOL /u01/emp.lst
SET LINESIZE 100
SET PAGESIZE 50

DROP USER MICR CASCADE;

DROP USER BEFTN CASCADE;

CREATE USER MICR IDENTIFIED BY ERA123MICR
DEFAULT TABLESPACE MICR
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON CHQIMG
QUOTA UNLIMITED ON MICR
QUOTA UNLIMITED ON MICRACK
QUOTA UNLIMITED ON MICRDFT
QUOTA UNLIMITED ON MICRIMG
QUOTA UNLIMITED ON MICRIND
QUOTA UNLIMITED ON MICRINW
QUOTA UNLIMITED ON MICRLOG
QUOTA UNLIMITED ON MICROUT
QUOTA UNLIMITED ON MICRSTP
QUOTA UNLIMITED ON MICRXML;

CREATE USER BEFTN IDENTIFIED BY ERA123BEFTN
DEFAULT TABLESPACE BEFTN
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON BEFTN;

GRANT CONNECT TO MICR, BEFTN;

GRANT DBA TO MICR, BEFTN;

GRANT RESOURCE TO MICR, BEFTN;

GRANT SELECT ON SYS.V_$SESSION TO MICR, BEFTN;

GRANT EXECUTE ON SYS.DBMS_CRYPTO TO MICR,BEFTN;

GRANT SELECT ON SYS.DBA_JOBS TO MICR;

GRANT SELECT ON SYS.DBA_JOBS_RUNNING TO MICR;

GRANT READ, WRITE ON DIRECTORY MY_IMAGE_ICE TO MICR;
GRANT READ, WRITE ON DIRECTORY EFT_XML TO BEFTN;
GRANT READ, WRITE ON DIRECTORY EFT_ORE TO BEFTN;
GRANT READ, WRITE ON DIRECTORY EFT_NOC TO BEFTN;
GRANT READ, WRITE ON DIRECTORY EFT_ICE TO BEFTN;
GRANT READ, WRITE ON DIRECTORY MY_IMAGE_DIR TO MICR;
GRANT READ, WRITE ON DIRECTORY MY_DATA_DIR TO MICR;
GRANT READ, WRITE ON DIRECTORY MY_XML TO MICR;
GRANT READ, WRITE ON DIRECTORY MY_ORE TO MICR;
GRANT READ, WRITE ON DIRECTORY MY_OCE TO MICR;
GRANT READ, WRITE ON DIRECTORY BB_IRE TO MICR;
GRANT READ, WRITE ON DIRECTORY BB_ICE TO MICR;
GRANT READ, WRITE ON DIRECTORY BB_ACK TO MICR;

EXEC DBMS_JAVA.grant_permission('MICR', 'java.io.FilePermission', '<<ALLFILES>>', 'read ,write, execute, delete');
EXEC DBMS_JAVA.grant_permission('MICR','SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC DBMS_JAVA.grant_permission('MICR','SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');

SPOOL OFF
EXIT;

EOF

EOO

# 6. Set Your Environment Veritable.

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=bkpdb
export PATH=$PATH:$ORACLE_HOME/bin

# 7. Import Backup To Backup Server.


'/u01/app/oracle/product/11.2.0/db_1/bin/impdp' system/system@bkpdb DIRECTORY=DMP_BKP TABLE_EXISTS_ACTION=REPLACE DUMPFILE=$DMP_FILE LOGFILE=IMP$LOG_FILE  SCHEMAS=MICR,BEFTN;