ANTYNET.PL – Administracja Sieci

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
   init[db_new].ora
   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]
   pfile=/oracle/product/10.2.0/db_1/dbs/init[db_new]aux.ora
   logfile
   ‘/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]
   DB_FILE_NAME_CONVERT=(’[target_path1]/’,’[aux_path]‘,
   ‘[target_path2]/’,’[aux_path]‘)
   pfile=[pfile_aux_path]/[db_new].ora
   logfile
   ‘[logfile_aux_path]/redo01.log’ size 100m,
   ‘[logfile_aux_path]/redo02.log’ size 100m,
   ‘[logfile_aux_path]/redo03.log’ size 100m;
   }

for more read documentation

how to add database to DBCA:
if you want to add database, which was made by hand “create database etc.” not automatically by dbca, you should update /etc/oratab

check:
SELECT * FROM dba_jobs;
SELECT * FROM dba_jobs_running;
SELECT schema_user,job,what,last_date,this_date,next_date,total_time/3600 hours,failures,broken FROM dba_jobs;
column TOTAL_TIME determine the time which system spend run this job from first initialization, it is not time of present us
column THIS_DATE is filled when job is still executing and show time present execution time

new job:
declare
jobnr number;
begin
dbms_job.submit(job=>jobnr,
what=>’dbms_output.put_line(”ble”);’,
next_date=>to_date(’10:00 09/09/2009′,’HH24:MI DD/MM/YYYY’),
interval=>’SYSDATE+1′);
end;
/

hand launch (only by owner, in the other case ORA-23421, even for sys user), beware on change next_date which will by calculated from now
example. interval: sysdate+1:
exec DBMS_JOB.RUN(JOB => [nr]);

set state BROKEN (only by owner, in the other case ORA-23421, even for sys user):
exec DBMS_JOB.BROKEN(JOB => [nr], BROKEN => TRUE);

unset state BROKEN (only by owner, in the other case ORA-23421, even for sys user):
exec DBMS_JOB.BROKEN(JOB => [nr], BROKEN => FALSE, NEXT_DATE => TO_DATE(’10:00 01/01/09′, ‘HH24:MI MM/DD/YY’));

change job (must be set all arguments, if any is unchange we set NULL):
exec DBMS_JOB.CHANGE(JOB => [nr], NEXT_DATE=>null, WHAT=>null, INTERVAL => ‘SYSDATE + 3′);

remove job (only by owner, in the other case ORA-23421, even for sys user):
exec DBMS_JOB.REMOVE(JOB => [nr]);

how to set time:
sysdate+1 #from present time + 1 day
trunc(sysdate)+1 #from 12.00 AM +1day
trunc(sysdate)+17/24 #17.00 PM today

check time:
SELECT to_char(trunc(sysdate+1) + 90/1440, ‘MM/DD/YYYY HH:MI AM’) FROM dual;