ORA-31634: job already exists

Error : Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted

tail: cannot open input
Compressing exportfile..

Solution :
1. select owner_name,job_name,operation,state from DBA_DATAPUMP_JOBS ;
2. select owner_name,job_name,operation,state from DBA_DATAPUMP_JOBS where owner_name=’SCHEMA_NAME’; and output like
SCHEMA_NAME SYS_EXPORT_SCHEMA_60 EXPORT NOT RUNNING
SCHEMA_NAME SYS_EXPORT_SCHEMA_03 EXPORT NOT RUNNING
3. If output count of the above query Is 99 then
4. Either change the script and execute it using user "SYSTEM" instead of provided SCHEMA_NAME or connect to the provided schema and drop table SYS_EXPORT_SCHEMA_01; to drop table SYS_EXPORT_SCHEMA_99; (use the below script
5. select 'DROP TABLE '||owner||'.'||table_name||';'
from dba_tables
where table_name like '%SYS%EXPORT%';

'DROPTABLE'||OWNER||'.'||TABLE_NAME||';'
-------------------------------------------------------------------------
DROP TABLE SYSTEM.SYS_EXPORT_FULL_01;

6. Re-execute the script.

The reason was : if the enteries in DBA_DATAPUMP_JOBS table become equals to 99 for any particular schema then "ORA-31634 : job already exists" occurs so make sure the entries must be below 99 in order to avoid above error .

Migrating to ASM Using RMAN

Migrating to ASM Using RMAN
The following method shows how a primary database can be migrated to ASM from a disk based backup:
Disable change tracking (only available in Enterprise Edition) if it is currently being used.
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
Shutdown the database.
SQL> SHUTDOWN IMMEDIATE
Modify the parameter file of the target database as follows:
Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.
Remove the CONTROL_FILES parameter from the spfile so the control files will be moved to the DB_CREATE_* destination and the spfile gets updated automatically. If you are using a pfile the CONTROL_FILES parameter must be set to the appropriate ASM files or aliases.
Start the database in nomount mode.
RMAN> STARTUP NOMOUNT
Restore the controlfile into the new location from the old location.
RMAN> RESTORE CONTROLFILE FROM 'old_control_file_name';
Mount the database.
RMAN> ALTER DATABASE MOUNT;
Copy the database into the ASM disk group.
RMAN> BACKUP AS COPY DATABASE FORMAT '+disk_group';
Switch all datafile to the new ASM location.
RMAN> SWITCH DATABASE TO COPY;
Open the database.
RMAN> ALTER DATABASE OPEN;
Create new redo logs in ASM and delete the old ones.

Enable change tracking if it was being used.
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

RMAN Backup Encryption

Oracle Database RMAN Backup Encryption



Purpose: To improve the Oracle backup security:


RMAN backups created as backup sets can be encrypted. Encrypted backups cannot be read if they are obtained by unauthorized people.

The minimum database version should be 10.2.0.

V$RMAN_ENCRYPTION_ALGORITHMS view contains a list of encryption algorithms supported by RMAN. If no encryption algorithm is specified, the default encryption algorithm is 128-bit AES.

RMAN Backup Encryption Modes:
1. Transparent Encryption of Backups
2. Password Encryption of Backups
3. Dual Mode Encryption of Backups

This concept of encryption/decryption also applies to Oracle RMAN (recovery manager) backup and recovery. The information needs to be encrypted using AES which encrypts data in 128,192,256 bits. It is necessary to set the encryption algorithm and supply password during recovery


RMAN> set encryption algorithm 'aes256' identified by password;
executing command: SET encryption
using target database control file instead of recovery catalog

RMAN> backup as compressed backupset database format \backup\oracle_backup_encryption.bkp';
Starting backup at 16-AUG-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
.....
RMAN> set decryption identified by password;