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.

How to Determine your Forms Patchset Level

There are supposedly two ways to determine your forms patchset level. The first method is to launch a form and click on Help -> About Oracle Applications. According to Note:74647.1 you should see it in the window that pops up. Maybe its just me, but I don't see it there.

Another method is to view the output by f60gen. Note:283985.1 provides a script which will determine the patchset level for you. You don't really need a script for it tho.

Execute f60gen and look at the first line:

oravisf@myserver=> f60gen
Forms 6.0 (Form Compiler) Version 6.0.8.27.0 (Production)



Subtract 9 from 27 and you'll have your patchset level. So I am on patchset level 18.

Part 4: (TAF) Transparent Application Failover and testing.

TAF, Transparent Application Failover is the ability for a connection to failover and reconnect to a surviving node. Transactions are rolled back but TAF can be configured to resume SELECT operations. Before you can use TAF you have to create a Service. Initially services were used for connection load balancing but over the years has developed into a method to distribute and manage workload across database instances. For more information on services see Oracles documentation:

http://www.oracle.com/technology/obe/10gr2_db_vmware/ha/rac/rac.htm

There are a few ways to create a service. You can use Grid Control, srvctl or dbca. For this exercise I am going to use DBCA.

Launch DBCA. Select Oracle Real Application Clusters database and click on next:

Select Services Management and click on Next:

RACDB should be selected, so click the Next button:

On the following screen click on the Add button. When prompted for a service name enter RAC and click on the OK button:



On the following screen verify both RAC instances (RACDB1, RACDB2) have the preferred option selected. Preferred means that the service will run on that instance when the environment first starts. An instance flagged as available means it will not run the service unless the preferred instances fail. Since this is a RAC environment we want users to be distributed between both nodes, thus they need to have the preferred option set.

The TAF policy is the failover settings. Select Basic. Basic means that the session is reconnected when a node fails. Preconnect means that a shadow process is created on another node in the cluster, to reduce time if there is a failure.

Once the changes above have been completed, click on the finish button. A popup will appear prompting you to configure the services. Select OK.

Once the service configuration has completed, you will be asked if you’d like to perform another operation. After you select No, dbca will close.

A check of the $ORACLE_HOME/network/admin/tnsnames.ora file will show the following new entry:

Lets break down this new entry and highlight a few key lines:
Line 5: (LOAD_BALANCE = yes)
This line indicates that Oracle will randomly select one of the addresses defined on the previously two lines and connect to that nodes listener. This is called client-side connect-time load balancing.

Lines 9 -13: These lines are the TAF settings.

Line 10: (TYPE = SELECT)
There are two types of failover. SELECT indicates that the session will be authenticated on a surviving node but as well SELECT statements will be re-executed. Rows already returned to the client are ignored.
The other option is SESSION. In a SESSION failover, only the users session is re-authenticated.
Line 11: (METHOD = BASIC)
BASIC means that the session will not reconnect to a surviving node until the failover occurs.
PRECONNECT means a shadow process is created on a backup instance to reduce failover time. There are some additional considerations when choosing this setting so be sure to read up on it.

Line 12 and 13: (RETRIES = 180)
(DELAY = 5)
Self-explanatory, the maximum number of retries to failover and the amount of time in seconds to wait between attempts.

Testing TAF

Session 1: Login to the database via the service created above:



Session 2: Login as sysdba and query gv$session to determine which instance Scott is connected to:



Scott is connected to instance 2 (which resides on raclinux2), so lets shutdown that instance and see what happens. As sysdba, connect to RACDB2 and shutdown immediate. Once the instance has shutdown re-execute the query above to see which instance Scott is now connected to:



Why hasn’t the session failed over? Because when node 2 was shutdown scott wasn’t executing a query. Re-execute the select count(*) from emp statement, then query gv$session again:

If a session is inactive, it will not failover until another statement is issued.

Part 3 - Issues and Resolutions

The following is a list of the issues and solutions for the problems I encountered while installing 10gR2 RAC , OEL5 on VMware. Some of the items below are things I tried but didn't resolve an issue but I thought might be interesting.

1. If you are using vmware on a slow computer, especially when 2 nodes are running, you may experience locking issues. If so, it could be timeouts for the shared disk. In your vmware config files add the following:

reslck.timeout="1200"

On the bottom right hand side of your vmware window you will see disk icons that will flash green when they are in use. If vmware hangs and any of the shared disks are green then this is probably the issue.

2. Eth0 has to be bridged or you will see the following running vipca or in your vip log:

Interface eth0 checked failed (host=raclinux1)
Invalid parameters, or failed to bring up VIP (host=raclinux1)

3. If during the install you didn’t disable the firewall, root.sh will be unable to start on the second node, raclinux2. If so, disable iptables by executing:

/sbin/service iptables stop

To disable this feature from starting after a reboot, execute the following as root:

/usr/sbin/system-config-services

Scroll down until you see iptables, if it is checked, remove the check then click on the save button. You can also stop the services from this program as well by highlighting iptables and clicking the stop button.

Subject: Root.sh Unable To Start CRS On Second Node Doc ID: Note:369699.1

4. The following two workarounds are already addressed in the installation guide but here they are for reference.

Subject: VIPCA FAILS COMPLAINING THAT INTERFACE IS NOT PUBLIC Doc ID: Note:316583.1
Subject: 10gR2 RAC Install issues on Oracle EL5 or RHEL5 or SLES10 (VIPCA Failures) Doc ID: Note:414163.1

5. Subject: CRS-0215: Could not start resource 'ora..vip' Doc ID: Note:356535.1

During a VIP status check, your public interfaces gateway is pinged. If you don't have a gateway specified, this check will fail. If that action fails it assumes there is a problem with the Ethernet adapter interface. To resolve this, change the parameter FAIL_WHEN_DEFAULTGW_NOT_FOUND in the $ORA_CRS_HOME/bin/racgvip and set it to 0

This doesn’t mean the VIP will failover, there are some additional checks. Also, the parameter FAIL_WHEN_DEFAULTGW_NOT_FOUND only applies if you don't have a gateway defined in your network setup. If you entered a gateway ip address as per my guide, even tho it may not be pingable, this will have no effect.

6. While troubleshooting VIP failovers I found the following note which details how to increase the timeouts for the VIP service. This didn’t solve any of the issues I encountered but I thought it may be interesting to note:

Subject: ASM Instance Shuts Down Cleanly On Its Own Doc ID: Note:277274.1

7. If you are using vmware on a slow computer you may experience a problem where the VIP’s failover frequently. If that happens you may want to set the following:
Increasing the value of the parameter CHECK_TIMES to 10 may help in this case.
In $ORA_CRS_HOME/bin/racgvip set the following line:

# number of time to check to determine if the interface is down
CHECK_TIMES=2

-- to --

# number of time to check to determine if the interface is down
CHECK_TIMES=10

NOTE: This will only help when the problem is because of slow response from the gateway. Please do NOT use this workaround in other situations. This will have sideaffect of increasing the time to detect a unresponsive public interface.

Subject: VIP Going Offline Intermittantly - Slow Response from Default Gateway Doc ID: Note:399213.1

8. CRS services in an unknown state after reboot. Watching the crs logs I noticed the services weren’t waiting for the appropriate timeout value before erroring. This is a bug with 10.2.0.1 CRS:

Patch 4698419

[10201-050630.0024A_LINUX64]STARTING CRS RESOURCES FAILS WITHOUT WAITING TIMEOUT

Part 2 – Installing OCR, ASM and Database

Pre-Req: Download and obtain 10.2.0.1 clusterware and 10.2.0.1 database from Technet.

1. Install CVUQDISK package.
   
   Unzip the clusterware software and cd to ./clusterware/rpm directory. Run the following commands on both nodes:
   
   [root@raclinux1 ~] export CVUQDISK_GRP=dba
   [root@raclinux1 ~] rpm –iv cvuqdisk-1.0.1-1.rpm
   Preparing packages for installation…
   cvuqdisk-1.0.1-1
   
   
2. Clusterware Install
   
   Change directory to /clusterware and execute:
   
   runInstaller -ignoreSysPreReqs
   
   
   
   Specify the inventory location and group name:
   
   
   
   Specify the Home details:
   
   
   
   Ignore warnings on the Prerequisite checks screen below. At the time 10.2.0.1 was released Oracle Enterprise Linux didn’t exist, so the installer doesn’t view it as a supported OS.
   
   
   
   Cluster Configuration. Click on Add and fill in the information for the second node, raclinux2 as per the screenshot below:
   
   
   
   Private Interconnect Enforcement… Select eth0, click the Edit button and select public for the interface type since it is the primary network card:
   
   
   
   Oracle Custer Registry (OCR) location. These have been mounted as /dev/raw/raw3 and /dev/raw/raw4:
   
   
   
   Voting Disk Locations: These have been mounted as /dev/raw/raw1, /dev/raw/raw2 and /dev/raw/raw6:
   
   
   
   Click Install on the Summary screen:
   
   
   
   Once the install has completed you will be prompted to execute the following scripts on both nodes
   
   
   
   The CRS Home root.sh script executes the ocrconfig (Oracle Cluster Registry Configuration Tool) and clscfg (Cluster Configuration tool). These scripts format the voting disks, startup the software and add the daemons to the inittab (OS startup scripts).
   
   Before executing root.sh, for both nodes, edit the vipca and srvctl files under the CRS bin directory. Search for the string LD_ASSUME_KERNEL and find the line where this variable is set. Unset the variable by placing the following on the next line:
   
   unset LD_ASSUME_KERNEL
   
   Set Note: 414163.1 for details.
   
   Execute root.sh. (Note: don’t return to the runInstaller and click OK signifying the root.sh script has finished until directed to do in a few steps.) Towards the end of the root.sh on raclinux2 output you will see the following error:
   
   Error 0(Native: listNetInterfaces:[3])
[Error 0(Native: listNetInterfaces:[3])]
   
   As per the same note above, on raclinux2 execute the following from the CRS bin directory as root:
   
   ./oifcfg setif –global eth0/192.168.0.0:public
   ./oifcfg setif –global eth1/10.10.10.0:cluster_interconnect
   
   
   
   From the same directory launch vipca on raclinux2:
   
   
   
   On the following screen enter the following information in the screenshot. When you put the IP Alias Name in, the IP Address column will auto populate:
   
   
   
   A Summary screen is show, on which you click on the Finish button:
   
   
   
   Once the config finishes you should see configuration results screen:
   
   
   
   From a command window, as oracle execute crs_stat –t from the Cluster Home bin directory and you should see that all services are online:
   
   
   
   Now, go back to raclinux1 and click OK that you have finished executing root.sh on both nodes. The following screen will appear and once each of the tools successfully complete click on next:
   
   
   
   The installation is now complete and you can click on Exit:
   
   
   
   
3. Install ASM.
   Unzip the database 10.2.0 archive and launch:
   
   runInstaller -ignoreSysPrereqs
   
   
   
   Select Enterprise Edition and click on next:
   
   
   
   Change the Oracle Home name and path to reflect this is an ASM install:
   
   
   
   The runInstaller will detect the cluster, so make sure raclinux2 is checked in the following screen:
   
   
   
   There will be some warnings in the Prerequisite check screen, these can be ignored for the same reason as the OCR install:
   
   
   
   Since we are performing an ASM install, select the Configure Automatic Storage Management (ASM) option and enter a password for the sys account:
   
   
   
   Configure ASM Storage: Since we are using ASMLib you should see the VOL1 you created earlier in the following screen. Select External Redundancy and the ORCL:VOL1 disk:
   
   
   
   Finally, click the install button:
   
   
   
   After the install you’ll be prompted to execute the root.sh scripts on both nodes. After which click on the OK button.
   
   
   
   Installation is now complete and you can click on exit:
   
   
   
   
4. Install Database Software. From the database software staging directory launch:
   
   runInstaller –ignoreSysPreReqs
   
   When prompted for Installation Type, select Enterprise edition and click on Next:
   
   
   
   Verify Home Details:
   
   
   
   Make sure both nodes are selected in the Cluster Installation Screen:
   
   
   
   Ignore warnings and click on Yes then on the next button:
   
   
   
   In the Select configuration Option screen select install database Software only:
   
   
   
   Review the Summary Screen and click on Install:
   
   
   
   Once the installation is complete you will be shown the following screen, click on Exit:
   
   
   
   
5. Install agent. Note: This assumes you already have grid control or access to a Grid Control installation. If you do not, then you can skip this step and manage the environment using Database Control. Download a copy of Enterprise manager and from raclinux1 launch the runInstaller:
   
   
   
   If you selected the Mass Agent download from OTN the only option available and preselected is “Additional Management Agent”. Click next and in the following screen modify the Parent Directory to: /home/oracle/product/10.2.0
   
   
   
   Since this is a clustered environment you will be prompted for a cluster or local install. Select cluster and verify both nodes are selected, and then click next.
   
   
   
   Select the location of an existing Grid Control Install:
   
   
   
   Click next and again on the next screen, ignore Oracle Configuration Manager Registration. On the last screen, review the summary and click on Install:
   
   
   
   Installing:
   
   
   
   When prompted, execute the root.sh script on each node, and in the correct order:
   
   
   
   After the installation, click exit:
   
   
   
   
6. Creating the cluster database.
   
   Change to the $ORACLE_HOME/bin directory and launch dbca. Select the option to create an Oracle Real Application Clusters database:
   
   
   
   Select Create a Database:
   
   
   
   Click on the Select All button to make sure both nodes are highlighted:
   
   
   
   Select the general purpose template:
   
   
   
   For the global database name and sid, enter RACDB:
   
   
   
   Select your grid control location in the following window. If you installed the agent earlier it will be automatically selected. If not, Use Database Control will be selected. Click next:
   
   
   
   Choose a password:
   
   
   
   Under storage options choose ASM:
   
   
   
   You’ll be promoted for the ASM sys password:
   
   
   
   Select the DATA Disk Group:
   
   
   
   Select Oracle-Managed Files:
   
   
   
   I didn’t create a second disk group for a flash back recovery area, so just click next on the following screen:
   
   
   
   Choose the sample schemas so you have some data to play with:
   
   
   
   You can create services now if you’d like or later via dbca or srvctl:
   
   
   
   You can customize the initialization parameters to your liking. I choose a custom SGA with 200 for the SGA and 25MB for the PGA. The rest were defaults:
   
   
   
   In the Database Storage window click on next:
   
   
   
   Finally, click finish to start the creation process:
   
   
   
   After you click the Finish button you will be prompted with a summary screen. You should review it to make sure everything looks fine then click on ok:
   
   
   
   If you selected the Generate Database Creation Script option, they will be generated first. Once it completes a popup will appear letting you know it was successful. After that click OK, you will be returned to the previous screen and click finish again:
   
   
   
   Once the install completes you will be presented with a screen similar to the one below: