how to clone database by RMAN:
it depends on recovering files from previous backup

  1. create PFILE for new database based on PFILE from source database
    main changes:
    • db_name
    • control_files
    • audit_file_dest
    • background_dump_dest
    • user_dump_dest
    • core_dump_dest
  2. create password file for new database:
    orapwd file=orapw[db_new] password=[pass]

  3. add new database to listener.ora and tnsnames.ora,

  4. create dirs for new database file,

  5. create directories for trace logs as admin with subdirectories: adump, udump, cdump, bdump,

  6. startup new auxiliary database in nomout mode(! then exit from your session because it will be hang on)

  7. start rman with ORACLE_SID = auxiliary database:

  8. at the auxiliary server run command:
    rman auxiliary / target sys/[sys_pass]@[targetdb_tnsalias] catalog rman/[rman_pass]@[catalogdb_tnsalias]
    you should get an output:
    connected to target database: [db_name] (DBID=[db_id])
    connected to recovery catalog database
    connected to auxiliary database: [db_name] (not mounted)

  9. run script:
    run {
    set newname for datafile 4 to '/oracle/oradata/[db_new]/users01.dbf';
    set newname for datafile 3 to '/oracle/oradata/[db_new]/sysaux01.dbf';
    set newname for datafile 2 to '/oracle/oradata/[db_new]/undotbs01.dbf';
    set newname for datafile 1 to '/oracle/oradata/[db_new]/system01.dbf';
    set newname for datafile 5 to '/oracle/oradata/[db_new]/example01.dbf';
    set newname for tempfile 1 to '/oracle/oradata/[db_new]/temp.dbf';
    duplicate target database to [db_new]
    '/oracle/oradata/[db_new]/redo01.log' size 50m,
    '/oracle/oradata/[db_new]/redo02.log' size 50m,
    '/oracle/oradata/[db_new]/redo03.log' size 50m;}

    to generate script above use sql at source db:
    spool clonescript.rcv
    select 'set newname for datafile '||file_id||' to '''||file_name||''';' from dba_data_files order by file_id;
    select 'set newname for tempfile '||file_id||' to '''||file_name||''';' from dba_temp_files order by file_id;
    spool off;
    and next modify output in vim:
    :% s/ora6\/oradata\/DBSRC/u01\/oradata\/DBAUX\/datafile
    or another script:
    run {
    set until time "to_date('2010-07-02:01:00','yyyy-mm-dd:hh24:mi')";
    configure default device type to sbt;
    configure device type sbt parallelism 2;
    configure auxiliary channel 1 device type sbt parms 'ENV=(NB_ORA_SERV=server_name,NB_ORA_CLIENT=server_target_name)';
    configure auxiliary channel 2 device type sbt parms 'ENV=(NB_ORA_SERV=server_name,NB_ORA_CLIENT=server_target_name)';
    duplicate target database to [db_new]
    '[logfile_aux_path]/redo01.log' size 100m,
    '[logfile_aux_path]/redo02.log' size 100m,
    '[logfile_aux_path]/redo03.log' size 100m;


if you get an output error: "...segmentation fault..." you have to check the alertlog file, and if you get "...database must be open in upgrade mode...", it means that you have tried clone between diferent versions of databases, so you shoud: alter databse recover; alter database open upgrade; @$ORACLE_HOME/rdbms/admin/catupgrd.sql @$ORACLE_HOME/rdbms/admin/utlrp.sql for more read documentation




  • SELECT * FROM dba_jobs
  • SELECT * FROM dba_jobs_running
  • column TOTAL_TIME determine the time which system spend run this job from first initialization, it is not time of present
  • column THIS_DATE is filled when job is still executing and show time present execution time

2 Create new job

  jobnr number;
    next_date=>to_date('10:00 09/09/2009','HH24:MI DD/MM/YYYY'),

3 Starting job by hand

  • must be run by job owner, otherwise ORA-23421, even for user sys, beware on change next_date which will by calculated from now
  • sql> exec DBMS_JOB.RUN(JOB => [nr]);

4 Turn off

  • set state BROKEN
  • sql> exec DBMS_JOB.BROKEN(JOB => [nr], BROKEN => TRUE);

5 Turn on

  • unset state BROKEN
  • sql> exec DBMS_JOB.BROKEN(JOB => [nr], BROKEN => FALSE, NEXT_DATE => TO_DATE(’10:00 01/01/09′, ‘HH24:MI MM/DD/YY’));

6 Changing job params

  • must be set all arguments, if any is unchange we set NULL:
  • sql> exec DBMS_JOB.CHANGE(JOB => [nr], NEXT_DATE => to_date('2011-01-01 10:00','YYYY-MM-DD HH24:MI'), WHAT => null, INTERVAL => ‘SYSDATE + 3′);

7 Removing job:

  • exec DBMS_JOB.REMOVE(JOB => [nr]);

8 dbms_ijob

  • DBMS_IJOB is a package included with Oracle that allows a sys or system user to manipulate other user's jobs.

9 How to set time:

sysdate+1from present time + 1day
trunc(sysdate)+1from 12.00 AM + 1day
trunc(sysdate)+17/2417:00 PM today<br />
trunc(sysdate)+60/14401:00 AM (60min/60min*24=1440)
  • sql> SELECT to_char(trunc(sysdate+1) + 90/1440, 'MM/DD/YYYY HH:MI AM') FROM dual;


10.1 ORA-23421

  • procedure dbms_job must by run by owner even sys user cant do that check dbms_ijob

10.2 ORA-12012

  • search above string ORA-12012 in alertlogs due to problem with previous jobs