Sunday, February 22, 2009

Adding a Node to a 10g RAC Cluster

PURPOSE
----------

The purpose of this note is to provide the user with a document that
can be used as a guide to add a cluster node from an Oracle 10g Real
Applications environment.

SCOPE & APPLICATION
--------------------------

This document can be used by DBAs and support analsyts who need to
either add a cluster node or assist another in adding a cluster
node in a 10g Unix Real Applications environment. If you are on
10gR2 (10.2.0.2 or higher), please refer to the documentation for
more updated steps.


ADDING A NODE TO A 10g RAC CLUSTER
--------------------------------------------

The most important steps that need to be followed are;

A. Configure the OS and hardware for the new node.
B. Add the node to the cluster.
C. Add the RAC software to the new node.
D. Reconfigure listeners for new node.
E. Add instances via DBCA.

Here is a breakdown of the above steps.


A. Configure the OS and hardware for the new node.
----------------------------------------------------

Please consult with available OS vendor documentation for this step.

See
Note 264847.1 for network requirements. Also verify that the OCR and
voting files are visible from the new node with correct permissions.


B. Add the node to the cluster.
------------------------------

1. If the CRS Home is owned by root and you are on a version < 10.1.0.4, change
the ownership of the CRS Home directories on all nodes to the Oracle user
so that OUI can read and write to these directories.
2. Set the DISPLAY environment variable and run the addNode.sh script from
$ORA_CRS_HOME/oui/bin on one of the existing nodes as the oracle user.
Example:

DISPLAY=ipaddress:0.0; export DISPLAY
cd $ORA_CRS_HOME/oui/bin
./addNode.sh

3. The OUI Welcome screen will appear, click next.

4. On the "Specify Cluster Nodes to Add to Installation" screen, add the
public and private node names (these should exist in /etc/hosts and
should be pingable from each of the cluster nodes), click next.

5. The "Cluster Node Addition Summary" screen will appear, click next.

6. The "Cluster Node Addition Progress" screen will appear. You will
then be prompted to run rootaddnode.sh as the root user. First verify
that the CLSCFG information in the rootaddnode.sh script is correct.
It should contain the new public and private node names and node
numbers. Example:

$CLSCFG -add -nn <node2>,2 -pn <node2-private>,2 -hn <node2>,2

Then run the rootaddnode.sh script on the EXISTING node you ran the
addNode.sh from. Example:

su root
cd $ORA_CRS_HOME
sh -x rootaddnode.sh

Once this is finished, click OK in the dialog box to continue.

7. At this point another dialog box will appear, this time you are
prompted to run $ORA_CRS_HOME/root.sh on all the new nodes.
If you are on version < 10.1.0.4 then
- Locate the highest numbered NEW cluster node using "$ORA_CRS_HOME/bin/olsnodes -n".
- Run the root.sh script on this highest numbered NEW cluster node.
- Run the root.sh script on the rest of the NEW nodes in any order.
For versions 10.1.0.4 and above the root scritps can be run on the NEW
nodes in any order.

Example:

su root
cd $ORA_CRS_HOME
sh -x root.sh

If there are any problems with this step, refer to
Note 240001.1

Once this is finished, click OK in the dialog box to continue.

8. After running the CRS root.sh on all new nodes, run
$ORA_CRS_HOME/bin/racgons add_config <newnode1>:4948 <newnode2>:4948...
from any node.

9. Next you will see the "End of Installation" screen. At this point you
may exit the installer.

10. Change the ownership of all CRS Homes back to root.


C. Add the Oracle Database software (with RAC option) to the new node.
-------------------------------------------------------------------------

1. On a pre-existing node, cd to the $ORACLE_HOME/oui/bin directory and
run the addNode.sh script. Example:

DISPLAY=ipaddress:0.0; export DISPLAY
cd $ORACLE_HOME/oui/bin
./addNode.sh

2. The OUI Welcome screen will appear, click next.

3. On the "Specify Cluster Nodes to Add to Installation" screen, specify
the node you want to add, click next.

4. The "Cluster Node Addition Summary" screen will appear, click next.

5. The "Cluster Node Addition Progress" screen will appear. You will
then be prompted to run root.sh as the root user.

su root
cd $ORACLE_HOME
./root.sh

Once this is finished, click OK in the dialog box to continue.

6. Next you will see the "End of Installation" screen. At this point you
may exit the installer.

7. Cd to the $ORACLE_HOME/bin directory and run the vipca tool with the
new nodelist. Example:

su root
DISPLAY=ipaddress:0.0; export DISPLAY
cd $ORACLE_HOME/bin
./vipca -nodelist <node1>,<node2>

8. The VIPCA Welcome Screen will appear, click next.

9. Add the new node's virtual IP information, click next.

10. You will then see the "Summary" screen, click finish.

11. You will now see a progress bar creating and starting the new CRS
resources. Once this is finished, click ok, view the configuration
results, and click on the exit button.

12. Verify that interconnect information is correct with:

oifcfg getif

If it is not correct, change it with:

oifcfg setif <interface-name>/<subnet>:<cluster_interconnectpublic>

For example:

oifcfg setif -global eth1/10.10.10.0:cluster_interconnect

or

oifcfg setif -node <nodename> eth1/10.10.10.0:cluster_interconnect


D. Reconfigure listeners for new node.
--------------------------------------

1. Run NETCA on the NEW node to verify that the listener is configured on
the new node. Example:

DISPLAY=ipaddress:0.0; export DISPLAY
netca

2. Choose "Cluster Configuration", click next.

3. Select all nodes, click next.

4. Choose "Listener configuration", click next.

5. Choose "Reconfigure", click next.

6. Choose the listener you would like to reconfigure, click next.

7. Choose the correct protocol, click next.

8. Choose the correct port, click next.

9. Choose whether or not to configure another listener, click next.

10. You may get an error message saying, "The information provided for this
listener is currently in use by another listener...". Click yes to
continue anyway.

11. The "Listener Configuration Complete" screen will appear, click next.

12. Click "Finish" to exit NETCA.

13. Run crs_stat to verify that the listener CRS resource was created.
Example:

cd $ORA_CRS_HOME/bin
./crs_stat

14. The new listener will likely be offline. Start it by starting the
nodeapps on the new node. Example:

srvctl start nodeapps -n <newnode>

15. Use crs_stat to confirm that all VIP's, GSD's, ONS's, and listeners are
ONLINE.


E. Add instances via DBCA. (for standby databases see section F)
-----------------------------------------------------------------

1. To add new instances, launch DBCA from a pre-existing node. Example:

DISPLAY=ipaddress:0.0; export DISPLAY
dbca

2. On the welcome screen, choose "Oracle Real Application Clusters",
click next.

3. Choose "Instance Management", click next.

4. Choose "Add an Instance", click next.

5. Choose the database you would like to add an instance to and specify
a user with SYSDBA privileges, click next. Click next again...

6. Choose the correct instance name and node, click next.

7. Review the storage screen, click next.

8. Review the summary screen, click OK and wait a few seconds for the
progress bar to start.

9. Allow the progress bar to finish. When asked if you want to perform
another operation, choose "No" to exit DBCA.

10. To verify success, log into one of the instances and query from
gv$instance, you should now see all nodes.


F. Adding Instances to the Standby Database
---------------------------------------------

1. If you are using a RAC primary, make sure the steps from section E have
been performed. If you are using a single instance primary, add the redo
log groups and threads to the primary database via "alter database"
commands. Example commands:

alter database add logfile thread 2
group 3 ('/dev/RAC/redo2_01_100.dbf') size 100M,
group 4 ('/dev/RAC/redo2_02_100.dbf') size 100M;
alter database enable public thread 2;


2. Create a new standby controlfile from the primary database and copy it
to the standby. Example commmand:

alter database create standby controlfile as "/u01/stby.ctl";

3. Shut down the standby database, back up the existing standby
controlfile on the standby database, and copy the new standby
controlfile into place.

4. Adjust any init.ora or spfile parameters such as thread, instance_name,
instance_number, local_listener, undo_tablespace, etc... for any new
instances.

5. Recover the standby database.

Tuesday, February 17, 2009

Post Cloning Setups (After Each Clone)

There are numerous post cloning activities that are performed on an instance taken from the production copy. Below are the specific steps we used to enable email events and the use of a generic email account.

1. Update wf_notifications
set status = 'CLOSED',mail_status = 'SENT',end_date = sysdate;

2. Update fnd_user
set email_address = 'TestOracle@yourdomain.com ';

3. Update per_people_f
set email_address = 'TestOracle@yourdomain.com ';

4. Update po_vendor_sites_all =
email_address = 'TestOracle@yourdomain.com',remittance_email = 'TestOracle@yourdomain.com';

5. Purge Workflow data :
Run the "Purge Obsolete Workflow Runtime Data" concurrent program

6. If test usernames are implemented, re-enable these accounts with the following code snippet:

UPDATE apps.fnd_user
SET last_update_date = SYSDATE, -- set update date
last_updated_by = -1, -- set to ANONYMOUS
password_accesses_left = NULL,
password_lifespan_accesses = NULL,
password_lifespan_days = NULL,
end_date = NULL -- activate user account
WHERE user_name = 'TEST_APPROVER I'
OR user_name = 'TEST_APPROVER II'
OR user_name = 'TEST_APPROVER III'
OR user_name = 'TEST_BUYER'
OR user_name = 'TEST_REQ'

7. Alerts can be selectively disabled or changed based on your preferences and the type of alert.

Wednesday, February 11, 2009

Control File Autobackups

It is very important for (some) recovery purposes to ensure you have a recent copy of your control file, especially if it contains your recovery catalog. There is one feature of control file autobackups which alot of dba's seem to be unfamiliar with, which is why I thought i'd write up a brief article on it. If you have this feature enabled and you make a structural change to the database, the control file is automatically backed up. ie, If you add a datafile, rename a file, etc. Information which could affect your ability to recover. This backup will be placed on disk even if your regular backup goes to tape.

A quick test. First, ensure you have control file autobackups enabled and select a location on disk. By default the control file backup will be placed in your $ORACLE_HOME/dbs directory unless you specify a location.

oravis@myserver=> rman target / nocatalog

Recovery Manager: Release 10.2.0.2.0 - Production on Wed Jan 16 14:48:49 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: VIS (DBID=767668735)
using target database control file instead of recovery catalog

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/bck/vis_backup/%F';

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/bck/vis_backup/%F';

A quick test to make sure the backup works and is on disk:

oravis@myserver=> rman target / nocatalog

Recovery Manager: Release 10.2.0.2.0 - Production on Wed Jan 16 15:25:24 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: VIS (DBID=767668735)using target database control file instead of recovery catalog

RMAN> backup current controlfile;

Starting backup at 16-JAN-08

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=414 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupsetincluding current control file in backupset
channel ORA_DISK_1: starting piece 1 at 16-JAN-08
channel ORA_DISK_1: finished piece 1 at 16-JAN-08piece handle=/mnt1/app/visdb/1020_64bit/dbs/01j6adrd_1_1 tag=TAG20080116T152532 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 16-JAN-08

Starting Control File Autobackup at 16-JAN-08
piece handle=/oradata/bck/vis_backup/c-767668735-20080116-00 comment=NONE

Finished Control File Autobackup at 16-JAN-08

RMAN> exit

Recovery Manager complete.

oravis@myserver=> ls -alrt /oradata/bck/vis_backup

total 42036

drwxrwxr-x 28 oravis dba 1024 Jan 16 14:47 ..
-rw-rw---- 1 oravis dba 19398656 Jan 16 15:25 c-767668735-20080116-00
drwxrwxr-x 2 oravis dba 512 Jan 16 15:25 .

Next we need to make a structural change to the database to see if the control file is automatically backed up. For this example i'll add a small datafile to the system tablespace.

SQL> alter tablespace SYSTEM add datafile '/oradata/dbf/visdata/system09.dbf' size 25M;

Tablespace altered.

A directory listing of the backup location should show a second control file backup:

oravis@myserver=> ls -alrt /oradata/bck/vis_backup

total 79972

drwxrwxr-x 28 oravis dba 1024 Jan 16 14:47 ..
-rw-rw---- 1 oravis dba 19398656 Jan 16 15:25 c-767668735-20080116-00
-rw-rw---- 1 oravis dba 19398656 Jan 16 15:26 c-767668735-20080116-01
drwxrwxr-x 2 oravis dba 512 Jan 16 15:26 .

Monday, February 2, 2009

How to shrink temporary tablespace if it goes huge

Goal:
Your production database has bi-annual or annual MIS reports program which performs huge sorting. Due that your Temp tablespace grows very much and occupies most of the space on file system. Now as the MIS reports process is run probably once or twice a year you don't want to keep a huge tempfile around for the rest of the 364 days and don't want to go out to buy disks.

You have created temp tablespace with datafiles as AUTOEXTEND ON MAXSIZE UNLIMITED to avoid Error: ORA-1652 Text: unable to extend temp segment by %s in tablespace %s

You have tried "alter database datafile .. resize" which always fails with Error: ORA 3297 : file contains blocks of data beyond requested RESIZE value.

You want to shrink the datafile to utilize the disk space for other tablespaces or any other purpose.

Fix:
Create new temporary tablespace with smaller size.

SQL>
create temporary tablespace TEMP
tempfile '/iaa/oradata/u01/TABSIAA/temp01.dbf' size 100M
extent management local uniform size 128K;

SQL> alter database default temporary tablespace temp2;

SQL> drop tablespace temp including contents and datafiles;

If any users are using the temporary tablespace currently, you would not be able to drop the tablespace. So, let us find out wihich users are using the temporary tablespace right now using bellow query:

SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)'M' "SIZE",
a.sid','a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;

Then kill or wait until the above users finish their jobs, and then try to drop the temporary tablespace again.

You can also see the usage of temporary tablespace using bellow query:

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE
from V$TEMP_SPACE_HEADER;

Cheers !