Thursday

Refreshing an 11i Database using Rman

In order to facilitate troubleshooting we maintain a test environment which is a nightly copy of our 11i production environment. Since this environment is usually used to test data fixes it has to be as up to date as possible. To perform the database refresh we use rman's duplicate feature.

The goal of this article isn't just to provide the entire set of scripts and send you on your way. I think its safe to say that most EBS environments aren't identical, so its not like you could take them and execute with no issues. Instead i'll highlight the steps we follow and some of the key scripts.

NOTE: This doesn't include any pre-setup steps such as, if this is the first time duplicating the database make sure you have the parameters db_file_name_convert and log_file_name_convert specified in your test environments init file.

  • Step 1: Shutdown the test environment. If you are using 10g then remove any tempfiles. In 10g, rman now includes tempfile information and if they exist you will encounter errors. Check this previous post. Startup the database in nomount mode.
  • Step 2: Build a Rman Script. There are a couple of ways to recover to a point in time and we have decided to use SCN numbers. Since this process needs to be automated, we query productions rman catalog and determine the proper SCN to use and build an rman script. Here it is:

    set feedback off
    set echo off
    set serverout on
    spool $HOME/scripts/prod_to_vis.sql
    declare
    vmax_fuzzy number;
    vmax_ckp number;
    scn number;
    db_name varchar2(3) := 'VIS';
    log_file_dest1 varchar2(30) := '/dbf/visdata/';
    begin
    select max(absolute_fuzzy_change#)+1,
    max(checkpoint_change#)+1
    into vmax_fuzzy, vmax_ckp
    from rc_backup_datafile;
    if vmax_fuzzy > vmax_ckp then
    scn := vmax_fuzzy;
    else
    scn := vmax_ckp;
    end if;
    dbms_output.put_line('run {');
    dbms_output.put_line('set until scn '||to_char(scn)||';');
    dbms_output.put_line('allocate auxiliary channel ch1 type disk;');
    dbms_output.put_line('allocate auxiliary channel ch2 type disk;');
    dbms_output.put_line('duplicate target database to '||db_name);
    dbms_output.put_line('logfile group 1 ('||chr(39)||log_file_dest1||'log01a.dbf'||chr(39)||',');
    dbms_output.put_line(chr(39)||log_file_dest1||'log01b.dbf'||chr(39)||') size 10m,');
    dbms_output.put_line('group 2 ('||chr(39)||log_file_dest1||'log02a.dbf'||chr(39)||',');
    dbms_output.put_line(chr(39)||log_file_dest1||'log02b.dbf'||chr(39)||') size 10m;}');
    dbms_output.put_line('exit;');
    end;
    /
    spool off;

    This script produces a spool file called, prod_to_vis.sql:

    run {
    set until scn 14085390202;
    allocate auxiliary channel ch1 type disk;
    allocate auxiliary channel ch2 type disk;
    duplicate target database to VIS
    logfile group 1 ('/dbf/visdata/log01a.dbf',
    '/dbf/visdata/log01b.dbf') size 10m,
    group 2 ('/oradata/dbf/visdata/log02a.dbf',
    '/dbf/visdata/log02b.dbf') size 10m;}
    exit;
    Note: Our production nightly backups are on disk which are NFS mounted to our test server.

  • Step 3: Execute the rman script. Launch rman, connect to the target, catalog, auxiliary and execute the script above:

    ie.
    rman> connect target sys/syspasswd@PROD catalog rmancat/catpasswd@REPO auxiliary /


    You may want to put some error checking around rman to alert you if it fails. We have a wrapper script which supplies the connection information and calls the rman script above. Our refresh is critical so if it fails we need to be paged.

    rman @$SCRIPTS/prod_to_vis.sql
    if [ $? != 0 ]
    then
    echo Failed
    echo "RMAN Dupcliate Failed!"|mailx -s "Test refresh failed" pageremail@mycompany.com
    exit 1
    fi


  • Step 4: If production is in archivelog mode but test isn't, then mount the database and alter database noarchivelog;
  • Step 5: If you are using a hotbackup for cloning then you need to execute adupdlib.sql. This updates libraries with correct OS paths. (Appendix B of Note:230672.1)
  • Step 6: Change passwords. For database accounts such as sys, system and other non-applications accounts change the passwords using alter user. For applications accounts such as apps/applsys, modules, sysadmin, etc use FNDCPASS to change their passwords.

    ie. To change the apps password:

    FNDCPASS apps/<production appspassword=> 0 Y system/<system_passwd> SYSTEM applsys <new apps passwd>

  • Step 7: Run autoconfig.
  • Step 8: Drop any database links that aren't required in the test environment, or repoint them to the proper test environments.
  • Step 9: Follow Section 3: Finishing Tasks of Note:230672.1
    • Update any profile options which have still reference the production instance.

      Example:

      UPDATE FND_PROFILE_OPTION_VALUES SET
      profile_option_value = REPLACE(profile_option_value,'PROD','TEST')
      WHERE profile_option_value like '%PROD%


      Specifically check the FND_PROFILE_OPTION_VALUES, ICX_PARAMETERS, WF_NOTIFICATION_ATTRIBUTES and WF_RESOURCES tables and look for production hostnames and ports. We also update the forms title bar with the date the environment was refreshed:

      UPDATE apps.FND_PROFILE_OPTION_VALUES SET
      profile_option_value = 'TEST:'||' Refreshed from '||'Production: '||SYSDATE
      WHERE profile_option_id = 125
      ;

    • Cancel Concurrent requests. We don't need concurrent requests which are scheduled in production to keep running in test. We use the following update to cancel them. Also, we change the number of processes for the standard manager.

      update fnd_concurrent_requests
      set phase_code='C',
      status_code='D'
      where phase_code = 'P'
      and concurrent_program_id not in (
      select concurrent_program_id
      from fnd_concurrent_programs_tl
      where user_concurrent_program_name like '%Synchronize%tables%'
      or user_concurrent_program_name like '%Workflow%Back%'
      or user_concurrent_program_name like '%Sync%responsibility%role%'
      or user_concurrent_program_name like '%Workflow%Directory%')
      and (status_code = 'I' OR status_code = 'Q');

      update FND_CONCURRENT_QUEUE_SIZE
      set min_processes = 4
      where concurrent_queue_id = 0;


  • Step 10: Perform any custom/environment specific steps. We have some custom modules which required some modifications as part of cloning.
  • Step 11: Startup all of the application processes. ($S_TOP/adstrtal.sh)

    NOTE: If you have an application tier you may have to run autoconfig before starting up the services.

Hopefully this article was of some use even tho it was pretty vague at times. If you have any questions feel free to ask. Any corrections or better methods don't hesitate to leave a comment either.

7 comments:

Anonymous said...

Hi,
Its nice to see step by step procedure.
I have a doubt here if we dont have catalog then how do we do this?

what I have done is I have started AUX Db in nomount status and then connect the source PROD DB and later I connected with AUX db using rman and execute RMAN Duplicate command.

Please clear me if I'm wrong and if possible provide me the scripts in mail.

Thanks,
Avdhesh

Dave said...

If your using the controlfile instead of a recovery catalog, then then only change would be to specifiy nocatalog :

rman> connect target sys/syspasswd@PROD nocatalog auxiliary /

Kumar said...

Hi
If I have an ASM instance then how to modify these steps? I have two instances i1 and i2 and I want to refresh i2 from i1.
i1 and i2 belong to the same asm instance.
I want to put i1 database in backup mode and use rman to copy the files over to i2 and do a Point in time recovery.
How will the steps given in the document change when I have ASM in picture.

Thank you
Kumar

pghdba said...

I'm new to 11i Apps (running 10.2) and wanted to know if RMAN is supported by Oracle for backup/recovery operations? We're getting into discussions on RMAN backups now and any advice would be appreciated.

Mehmood said...

nice post, keep the good work up.

cheers

Dave said...

Rman is supported... As for ASM, I haven't used it with EBS yet.

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.