Cleaning up after a Failed Clusterware Install

Posted at Thursday, September 18, 2008
I normally use the following script when cleaning up after a failed Cluster Ready Services (Clusterware) install.

In my example, I remove the dependent files and also the entire Oracle Inventory and Binaries directories: this script is Linux-specific.

$ ./remove_crs_files.sh

remove_crs_files.sh

rm -Rf /etc/oracle/*
rm -f /etc/init.d/init.cssd
rm -f /etc/init.d/init.crs
rm -f /etc/init.d/init.crsd
rm -f /etc/init.d/init.evmd
rm -f /etc/rc2.d/K96init.crs
rm -f /etc/rc2.d/S96init.crs
rm -f /etc/rc3.d/K96init.crs
rm -f /etc/rc3.d/S96init.crs
rm -f /etc/rc5.d/K96init.crs
rm -f /etc/rc5.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -f /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab
rm -Rf /u01/app/crs
mkdir /u01/app/crs
chown oracle:dba /u01/app/crs
rm -Rf /u01/app/oracle/oraInventory
mkdir /u01/app/oracle/oraInventory
chown oracle:dba /u01/app/oracle/oraInventory

For more information, reference the following Metalink Article: 239998.1


Labels:

How to Migrate an Existing RAC database to ASM

Posted at Sunday, May 25, 2008
This post covers how to migrate an existing RAC database to use ASM storage. In my case, I use a 2-node, 10gR2 database on Linux that is currently using OCFS2 filesystems for the database's files. I will migrate the database to ASM using ASMLib on top of iSCSI volumes. This example assumes that you have already prepared the relevant storage devices for recognition by the O/S.

Prepare the Storage

As root on one of the hosts, first identify the device names to format:

# ./iscsi-map.sh
Host / SCSI ID SCSI Device Name iSCSI Target Name
---------------- ----------------------- -----------------
2 /dev/sdb asm2
3 /dev/sde asm1
4 /dev/sdc arch
5 /dev/sdf software
6 /dev/sdd logs
7 /dev/sdg ora2
8 /dev/sdi ora1
9 /dev/sdh crs

Format the devices you plan to use for the ASM migration:

# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.


The number of cylinders for this disk is set to 25024.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-25024, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-25024, default 25024):
Using default value 25024

Command (m for help): p

Disk /dev/sdb: 26.2 GB, 26239565824 bytes
64 heads, 32 sectors/track, 25024 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes

Device Boot Start End Blocks Id System
/dev/sdb1 1 25024 25624560 83 Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.


# fdisk /dev/sde
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.


The number of cylinders for this disk is set to 25024.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-25024, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-25024, default 25024):
Using default value 25024

Command (m for help): p

Disk /dev/sde: 26.2 GB, 26239565824 bytes
64 heads, 32 sectors/track, 25024 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes

Device Boot Start End Blocks Id System
/dev/sde1 1 25024 25624560 83 Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

Probe each host to pickup the changes via:

# partprobe
# fdisk -l


Configure ASMLib on each host via:

# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting without typing an
answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: [ OK ]
Loading module "oracleasm": [ OK ]
Mounting ASMlib driver filesystem: [ OK ]
Scanning system for ASM disks: [ OK ]

On one node as root, create the ASM Volumes via:

# /etc/init.d/oracleasm createdisk VOL1 /dev/sdb1
Marking disk "/dev/sdb1" as an ASM disk: [ OK ]


# /etc/init.d/oracleasm createdisk VOL2 /dev/sde1
Marking disk "/dev/sde1" as an ASM disk: [ OK ]

On all other nodes, scan to recognize the changes via:

# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks: [ OK ]


# /etc/init.d/oracleasm listdisks
VOL1
VOL2

Create the ASM Instances

Create directories for the clustered ASM instance on shared storage. In my case, I will put it on the same OCFS2 volumes hosting the voting and cluster registry files for CRS.

# mkdir -p /u02/oracle/crs/asm
# mkdir -p /u02/oracle/crs/asm/bdump
# mkdir -p /u02/oracle/crs/asm/cdump
# mkdir -p /u02/oracle/crs/asm/udump
# chown -R oracle:dba /u02/oracle/crs/asm

Create the initial ASM instance parameter file on the shared storage:

# touch /u02/oracle/crs/asm/init+ASM.ora
# vi /u02/oracle/crs/asm/init+ASM.ora


init+ASM.ora

*.asm_diskgroups='DATA','BACKUPS'
*.asm_diskstring='/dev/oracleasm/disks/*'
*.background_dump_dest='/u02/oracle/crs/asm/bdump'
*.cluster_database=true
*.core_dump_dest='/u02/oracle/crs/asm/cdump'
+ASM2.instance_number=2
+ASM1.instance_number=1
*.instance_type='asm'
*.large_pool_size=20M
*.remote_login_passwordfile='exclusive'
*.user_dump_dest='/u02/oracle/crs/asm/udump'

Link to the parameter file from each host:

# su - oracle
# cd $ORACLE_HOME/dbs
# ln -s /u02/oracle/crs/asm/init+ASM.ora init+ASM1.ora


# su - oracle
# cd $ORACLE_HOME/dbs
# ln -s /u02/oracle/crs/asm/init+ASM.ora init+ASM2.ora

Create the passwordfile on shared storage and link to it from each ASM instance:

# orapwd file=/u02/oracle/crs/asm/orapw+ASM password={password}


Link to the passwordfile on each node:

# cd $ORACLE_HOME/dbs
# ln -s /u02/oracle/crs/asm/orapw+ASM orapw+ASM1


# cd $ORACLE_HOME/dbs
# ln -s /u02/oracle/crs/asm/orapw+ASM orapw+ASM2

Update /etc/oratab on each node:

# echo "+ASM1:/u01/app/oracle/product/10.2:N" >> /etc/oratab


# echo "+ASM2:/u01/app/oracle/product/10.2:N" >> /etc/oratab

Create the ASM Diskgroups:

# export ORACLE_SID=+ASM1
# . oraenv
# sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Sun May 25 16:52:56 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup mount;
ASM instance started

Total System Global Area 100663296 bytes
Fixed Size 1260312 bytes
Variable Size 74237160 bytes
ASM Cache 25165824 bytes
ORA-15032: not all alterations performed
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA"
ORA-15063: ASM discovered an insufficient number of disks for diskgroup
"BACKUPS"

SQL> create diskgroup DATA external redundancy disk '/dev/oracleasm/disks/VOL1';

Diskgroup created.

SQL> create diskgroup BACKUPS external redundancy disk '/dev/oracleasm/disks/VOL2';

Diskgroup created.

Now that you have confirmed that the ASM instance is functional, create the spfile from pfile:

SQL> create spfile='/u02/oracle/crs/asm/spfile+ASM.ora' from pfile;

File created.

Update each node to reflect use of the spfile:

# cd $ORACLE_HOME/dbs
# vi init+ASM1.ora

init+ASM1.ora

spfile='/u02/oracle/crs/asm/spfile+ASM.ora'

# cd $ORACLE_HOME/dbs
# vi init+ASM2.ora

init+ASM2.ora

spfile='/u02/oracle/crs/asm/spfile+ASM.ora'

Register the clustered ASM instance with srvctl

# srvctl add asm -n rac1 -i +ASM1 -o $ORACLE_HOME
# srvctl add asm -n rac2 -i +ASM2 -o $ORACLE_HOME
# srvctl enable asm -n rac1 -i +ASM1
# srvctl enable asm -n rac2 -i +ASM2

Start and verify ASM instances:

# srvctl start asm -n rac1
# srvctl start asm -n rac2
# srvctl status asm -n rac2
ASM instance +ASM2 is running on node rac2.

# srvctl status asm -n rac1
ASM instance +ASM1 is running on node rac1.

Prepare to migrate the database in question to use ASM as storage

Query the names of the database files:

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oracle/ora1/em/system01.dbf
/u02/oracle/ora1/em/undotbs01.dbf
/u02/oracle/ora1/em/sysaux01.dbf
/u02/oracle/ora1/em/users01.dbf
/u02/oracle/ora1/em/example01.dbf
/u02/oracle/ora1/em/undotbs02.dbf
/u02/oracle/ora2/em/mgmt.dbf
/u02/oracle/ora1/em/mgmt_ecm_depot1.dbf

8 rows selected.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u02/oracle/ora1/em/control01.ctl
/u02/oracle/ora2/em/control02.ctl

SQL> select * from gv$logfile;

INST_ID GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ------- ------- ----------------------------------- ---
1 2 ONLINE /u02/oracle/ora1/em/redo_1_2a.log NO
1 2 ONLINE /u02/oracle/ora2/em/redo_1_2b.log NO
1 1 ONLINE /u02/oracle/ora1/em/redo_1_1a.log NO
1 1 ONLINE /u02/oracle/ora2/em/redo_1_1b.log NO
1 3 ONLINE /u02/oracle/ora1/em/redo_2_3a.log NO
1 3 ONLINE /u02/oracle/ora2/em/redo_2_3b.log NO
1 4 ONLINE /u02/oracle/ora1/em/redo_2_4a.log NO
1 4 ONLINE /u02/oracle/ora2/em/redo_2_4b.log NO
2 2 ONLINE /u02/oracle/ora1/em/redo_1_2a.log NO
2 2 ONLINE /u02/oracle/ora2/em/redo_1_2b.log NO
2 1 ONLINE /u02/oracle/ora1/em/redo_1_1a.log NO

INST_ID GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ------- ------- ----------------------------------- ---
2 1 ONLINE /u02/oracle/ora2/em/redo_1_1b.log NO
2 3 ONLINE /u02/oracle/ora1/em/redo_2_3a.log NO
2 3 ONLINE /u02/oracle/ora2/em/redo_2_3b.log NO
2 4 ONLINE /u02/oracle/ora1/em/redo_2_4a.log NO
2 4 ONLINE /u02/oracle/ora2/em/redo_2_4b.log NO

16 rows selected.

Migrate the Database to ASM

Update the control_files parameter in preparation for migration:

$ srvctl stop database -d em
[oracle@rac1 dbs]$ export ORACLE_SID=em1
[oracle@rac1 dbs]$ . oraenv
[oracle@rac1 dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Sun May 25 17:38:38 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size 1261300 bytes
Variable Size 243269900 bytes
Database Buffers 16777216 bytes
Redo Buffers 7127040 bytes

SQL> alter system set control_files='+DATA/control.ctl' scope=spfile sid='*';

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

Migrate the controlfile:

# rman target=/

Recovery Manager: Release 10.2.0.3.0 - Production on Sun May 25 17:40:41 2008

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 268435456 bytes

Fixed Size 1261300 bytes
Variable Size 247464204 bytes
Database Buffers 12582912 bytes
Redo Buffers 7127040 bytes

RMAN> restore controlfile from '/u02/oracle/ora1/em/control01.ctl';

Starting restore at 25-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 instance=em1 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATA/control.ctl
Finished restore at 25-MAY-08

Migrate the Datafiles:

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> backup as copy database format '+DATA';

Starting backup at 25-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 instance=em1 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/u02/oracle/ora2/em/mgmt.dbf
output filename=+DATA/em/datafile/mgmt_tablespace.257.655667175 tag=TAG20080525T174614 recid=10 stamp=655668164
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:16:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u02/oracle/ora1/em/system01.dbf
output filename=+DATA/em/datafile/system.258.655668171 tag=TAG20080525T174614 recid=11 stamp=655668229
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u02/oracle/ora1/em/sysaux01.dbf
output filename=+DATA/em/datafile/sysaux.259.655668235 tag=TAG20080525T174614 recid=12 stamp=655668262
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u02/oracle/ora1/em/undotbs02.dbf
output filename=+DATA/em/datafile/undotbs2.260.655668271 tag=TAG20080525T174614 recid=13 stamp=655668293
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u02/oracle/ora1/em/undotbs01.dbf
output filename=+DATA/em/datafile/undotbs1.261.655668297 tag=TAG20080525T174614 recid=14 stamp=655668309
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u02/oracle/ora1/em/example01.dbf
output filename=+DATA/em/datafile/example.262.655668311 tag=TAG20080525T174614 recid=15 stamp=655668322
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00008 name=/u02/oracle/ora1/em/mgmt_ecm_depot1.dbf
output filename=+DATA/em/datafile/mgmt_ecm_depot_ts.263.655668327 tag=TAG20080525T174614 recid=16 stamp=655668337
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u02/oracle/ora1/em/users01.dbf
output filename=+DATA/em/datafile/users.264.655668341 tag=TAG20080525T174614 recid=17 stamp=655668352
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA/em/controlfile/backup.265.655668357 tag=TAG20080525T174614 recid=18 stamp=655668358
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 25-MAY-08
channel ORA_DISK_1: finished piece 1 at 25-MAY-08
piece handle=+DATA/em/backupset/2008_05_25/nnsnf0_tag20080525t174614_0.266.655668361 tag=TAG20080525T174614 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 25-MAY-08

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/em/datafile/system.258.655668171"
datafile 2 switched to datafile copy "+DATA/em/datafile/undotbs1.261.655668297"
datafile 3 switched to datafile copy "+DATA/em/datafile/sysaux.259.655668235"
datafile 4 switched to datafile copy "+DATA/em/datafile/users.264.655668341"
datafile 5 switched to datafile copy "+DATA/em/datafile/example.262.655668311"
datafile 6 switched to datafile copy "+DATA/em/datafile/undotbs2.260.655668271"
datafile 7 switched to datafile copy "+DATA/em/datafile/mgmt_tablespace.257.655667175"
datafile 8 switched to datafile copy "+DATA/em/datafile/mgmt_ecm_depot_ts.263.655668327"

Open the Database by exiting RMAN and then issuing the following via SQL*Plus:

# SQL> alter database open;

Database altered.

Startup the other instances:

# srvctl start instance -d em -i em2

Create a new Temporary Tablespace on the ASM Diskgroup:

SQL> CREATE TEMPORARY TABLESPACE TEMPTBS TEMPFILE '+DATA' SIZE 200M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTBS;

Database altered.

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Create new Online Redo Log Groups:

SQL> alter system archive log all;
alter system archive log all
*
ERROR at line 1:
ORA-00271: there are no logs that need archiving

SQL> alter system archive log stop;

System altered.

Create ASM Directories

# export ORACLE_SID=+ASM1
# . oraenv
# sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Sun May 25 18:32:31 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> alter diskgroup DATA add directory '+DATA/em/log1';

Diskgroup altered.

SQL> alter diskgroup DATA add directory '+DATA/em/log2';

Diskgroup altered.

Create New Online Redo Log Groups for each public thread before dropping the old ones

SQL> alter database add logfile thread 1 group 5
2 ('+DATA/em/log1/log_1_5a.log','+DATA/em/log1/log_1_5b.log') size 10M;

Database altered.

SQL> alter database add logfile thread 1 group 6
2 ('+DATA/em/log1/log_1_6a.log','+DATA/em/log1/log_1_6b.log') size 10M;

Database altered.

SQL> alter database add logfile thread 1 group 7
2 ('+DATA/em/log1/log_1_7a.log','+DATA/em/log1/log_1_7b.log') size 10M;

Database altered.

SQL> alter database add logfile thread 2 group 8
2 ('+DATA/em/log2/log_2_8a.log','+DATA/em/log2/log_2_8b.log') size 10M;

Database altered.

SQL> alter database add logfile thread 2 group 9
2 ('+DATA/em/log2/log_2_9a.log','+DATA/em/log2/log_2_9b.log') size 10M;

Database altered.

SQL> alter database add logfile thread 2 group 10
2 ('+DATA/em/log2/log_2_10a.log','+DATA/em/log2/log_2_10b.log') size 10M;

Database altered.

Drop the Old Online Redo Log Groups using the traditional process, for example:

SQL>  alter system switch logfile;


System altered.

SQL> select group#, thread#, status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
5 1 ACTIVE
6 1 ACTIVE
7 1 CURRENT
8 2 CURRENT
9 2 ACTIVE
10 2 ACTIVE

7 rows selected.

SQL> alter database drop logfile group 1;

Database altered.

Optionally, drop the old datafile images:

# rman target=/

Recovery Manager: Release 10.2.0.3.0 - Production on Sun May 25 19:00:45 2008

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

connected to target database: EM (DBID=1167076574)

RMAN> delete copy of database;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=124 instance=em1 devtype=DISK

List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
19 1 A 25-MAY-08 2754400 25-MAY-08 /u02/oracle/ora1/em/system01.dbf
20 2 A 25-MAY-08 2754400 25-MAY-08 /u02/oracle/ora1/em/undotbs01.dbf
21 3 A 25-MAY-08 2754400 25-MAY-08 /u02/oracle/ora1/em/sysaux01.dbf
22 4 A 25-MAY-08 2754400 25-MAY-08 /u02/oracle/ora1/em/users01.dbf
23 5 A 25-MAY-08 2754400 25-MAY-08 /u02/oracle/ora1/em/example01.dbf
24 6 A 25-MAY-08 2754400 25-MAY-08 /u02/oracle/ora1/em/undotbs02.dbf
25 7 A 25-MAY-08 2754400 25-MAY-08 /u02/oracle/ora2/em/mgmt.dbf
26 8 A 25-MAY-08 2754400 25-MAY-08 /u02/oracle/ora1/em/mgmt_ecm_depot1.dbf

Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/system01.dbf recid=19 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/undotbs01.dbf recid=20 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/sysaux01.dbf recid=21 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/users01.dbf recid=22 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/example01.dbf recid=23 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/undotbs02.dbf recid=24 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora2/em/mgmt.dbf recid=25 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/mgmt_ecm_depot1.dbf recid=26 stamp=655668498
Deleted 8 objects

Migrate the Archive Log Destinations to ASM:

SQL> alter system set log_archive_dest_1='LOCATION=+BACKUPS' scope=BOTH sid='*';

System altered.

SQL> alter system archive log start;

System altered.

SQL> alter system archive log current;

System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +BACKUPS
Oldest online log sequence 342
Next log sequence to archive 344
Current log sequence 344

At this point, you should be able to query an ASM instance and find archivelogs in the BACKUPS diskgroup:

SQL> select count(*) from v$asm_file where type='ARCHIVELOG' and group_number = (select group_number from v$asm_diskgroup where name='BACKUPS');

COUNT(*)
----------
4



Labels: , ,

How to Backup and Restore a RAC Database

Posted at Saturday, May 24, 2008
This example illustrates how to backup and recover a 2-node, 10gR2 RAC cluster on Linux. Backup and recovery operations for RAC databases are similar to that of single instance databases, however there are some key differences - the goal of this post is to highlight the steps required for recoverying RAC databases.

Backup the Database

Use the method you prefer. I used a NOCATALOG, disk-based backup for this example, using the scripts found here.

Simulate the Failure

Shutdown the database and simulate a failure by dropping and/or corrupting a datafile.

# export ORACLE_SID=em1
# . oraenv
# srvctl stop database -d em
# srvctl status database -d em
Instance em1 is not running on node rac1
Instance em2 is not running on node rac2


# cd /u02/oracle/ora1/em
# rm sysaux01.dbf


Verify the Failure

# srvctl start instance -d em -i em1
PRKP-1001 : Error starting instance em1 on node rac1
CRS-0215: Could not start resource 'ora.em.em1.inst'.

You will find something similar to the following in the instance's alert log:

Sat May 24 16:53:47 2008
Errors in file /u02/oracle/logs/em/bdump/em1_dbw0_16947.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u02/oracle/ora1/em/sysaux01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN...
Sat May 24 16:53:49 2008
Shutting down instance (abort)

Restore the Database

First, take the database out of cluster mode via:

# sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Sat May 24 17:02:17 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size 1261300 bytes
Variable Size 251658508 bytes
Database Buffers 8388608 bytes
Redo Buffers 7127040 bytes

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

Then restore the database via RMAN:

# rman target=/

Recovery Manager: Release 10.2.0.3.0 - Production on Sat May 24 17:04:10 2008

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area 268435456 bytes

Fixed Size 1261300 bytes
Variable Size 251658508 bytes
Database Buffers 8388608 bytes
Redo Buffers 7127040 bytes

RMAN> restore database;

Starting restore at 24-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oracle/ora1/em/system01.dbf
restoring datafile 00002 to /u02/oracle/ora1/em/undotbs01.dbf
restoring datafile 00003 to /u02/oracle/ora1/em/sysaux01.dbf
restoring datafile 00004 to /u02/oracle/ora1/em/users01.dbf
restoring datafile 00005 to /u02/oracle/ora1/em/example01.dbf
restoring datafile 00006 to /u02/oracle/ora1/em/undotbs02.dbf
restoring datafile 00007 to /u02/oracle/ora2/em/mgmt.dbf
restoring datafile 00008 to /u02/oracle/ora1/em/mgmt_ecm_depot1.dbf
channel ORA_DISK_1: reading from backup piece /u02/oracle/ora3/em/backups/0ijh6j4t_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oracle/ora3/em/backups/0ijh6j4t_1_1 tag=FHB_EM1200805241630
channel ORA_DISK_1: restore complete, elapsed time: 00:06:36
Finished restore at 24-MAY-08

Since my database is so small, I will simply restore the entire thing, however, since you know which datafile is missing, you could simply restore it and then recover the database as necessary.

Recover the Database

RMAN> recover database;

Starting recover at 24-MAY-08
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 24-MAY-08

RMAN> alter database open;

database opened
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Afterwards, place the database back into cluster mode and startup both instances:

# sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Sat May 24 17:16:36 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> alter system set cluster_database=true scope=spfile sid='*';

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


# srvctl start database -d em
[oracle@rac1 bdump]$ srvctl status database -d em
Instance em1 is running on node rac1
Instance em2 is running on node rac2



Labels: ,

Creating a RAC Database Manually (without dbca)

Posted at Thursday, February 28, 2008
If you would like to by-pass the Database Configuration Assistant (dbca), you can use the following example - with appropriate alterations - to create your RAC databases. This example assumes that you already have a cluster with the appropriate clusterware and shared storage, etc. configured.

If not already present, create listeners on each node; for example:

On node1:

LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1525)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1525)(IP = FIRST))
)
)

On node2:

LISTENER_RAC2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1525)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1525)(IP = FIRST))
)
)

I start with an area of shared storage for the files shared by the instances:

# mount | grep /u02/app/oracle
/dev/sdb1 on /u02/app/oracle type ocfs2 (rw,_netdev,heartbeat=local)

As you can see, I am using an ocfs2 filesystem for this purpose. I will create the necessary files here and create the appropriate symbolic links from each $ORACLE_HOME - this example does not use a shared $ORACLE_HOME.

Create the following files in your shared directory as appropriate: tnsnames.ora, sqlnet.ora, orapw${ORACLE_SID}, init.ora.

Example tnsnames.ora

LISTENER_RAC2 =
(address = (protocol = tcp)(host = rac2-vip)(port = 1525))


LISTENER_RAC1 =
(address = (protocol = tcp)(host = rac1-vip)(port = 1525))


JLC =
(DESCRIPTION =
(ADDRESS_LIST =
(address = (protocol = tcp)(host = rac1-vip)(port = 1525))
(address = (protocol = tcp)(host = rac2-vip)(port = 1525))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVER = dedicated)
(SERVICE_NAME = jlc.colestock.test)
(failover_mode =
(type = select)
(method = basic)
(retries = 180)
(delay = 5)
)
)
)

LISTENERS_JLC =
(address_list =
(address = (protocol = tcp)(host = rac1-vip)(port = 1525))
(address = (protocol = tcp)(host = rac2-vip)(port = 1525))
)

JLC2 =
(DESCRIPTION =
(ADDRESS_LIST =
(address = (protocol = tcp)(host = rac2-vip)(port = 1525))
)
(CONNECT_DATA =
(SERVICE_NAME = jlc.colestock.test)
(INSTANCE_NAME = jlc2)
)
)

Example sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES)


Example init.ora

*.db_name=jlc
*.db_block_size=8192
*.db_domain=colestock.test
*.service_names='jlc.colestock.test'
*.diagnostic_dest=/u02/app/oracle
jlc1.instance_name=jlc1
jlc2.instance_name=jlc2
*.memory_target=360M
*.control_files=("/u02/app/oradata01/jlc/control01.ctl","/u02/app/oradata02/jlc/control02.ctl")
jlc1.local_listener=LISTENER_RAC1
jlc2.local_listener=LISTENER_RAC2
*.remote_listener=LISTENERS_JLC
jlc1.thread=1
jlc2.thread=2
jlc1.undo_tablespace=UNDOTBS1
jlc2.undo_tablespace=UNDOTBS2
*.cluster_database=false
jlc1.instance_number=1
jlc2.instance_number=2
jlc1.instance_name=jlc1
jlc2.instance_name=jlc2
*.undo_management=auto
*.compatible='11.1.0'

Once the aforementioned files are present in your shared directory, create symbolical links to each $ORACLE_HOME; for example:

On node1:

# ls -lar $ORACLE_HOME/dbs
lrwxrwxrwx 1 oracle dba 29 Feb 27 22:28 spfilejlc1.ora -> /u02/app/oracle/spfilejlc.ora
lrwxrwxrwx 1 oracle dba 24 Feb 27 21:06 orapwjlc1 -> /u02/app/oracle/orapwjlc
lrwxrwxrwx 1 oracle dba 27 Feb 27 21:24 initjlc1.ora -> /u02/app/oracle/initjlc.ora


# ls -lar $TNS_ADMIN
lrwxrwxrwx 1 oracle dba 28 Feb 27 21:00 tnsnames.ora -> /u02/app/oracle/tnsnames.ora
lrwxrwxrwx 1 oracle dba 26 Feb 27 21:00 sqlnet.ora -> /u02/app/oracle/sqlnet.ora

On node2:

# ls -lar $ORACLE_HOME/dbs
lrwxrwxrwx 1 oracle dba 29 Feb 27 22:25 spfilejlc2.ora -> /u02/app/oracle/spfilejlc.ora
lrwxrwxrwx 1 oracle dba 24 Feb 27 21:03 orapwjlc2 -> /u02/app/oracle/orapwjlc
lrwxrwxrwx 1 oracle dba 27 Feb 27 20:29 initjlc2.ora -> /u02/app/oracle/initjlc.ora


# ls -lart $TNS_ADMIN
lrwxrwxrwx 1 oracle dba 28 Feb 27 20:45 tnsnames.ora -> /u02/app/oracle/tnsnames.ora
lrwxrwxrwx 1 oracle dba 26 Feb 27 20:49 sqlnet.ora -> /u02/app/oracle/sqlnet.ora

Notice I have created a symbolic link to an spfile which has yet to be created - we will create this after creating the database.

Create the database (editing script as appropriate for your environment):

create_db_jlc.sql

shutdown immediate;
startup nomount;

CREATE DATABASE
CONTROLFILE REUSE
MAXINSTANCES 32
MAXLOGHISTORY 10000
MAXLOGMEMBERS 5
MAXLOGFILES 64
DATAFILE '/u02/app/oradata01/jlc/system01.dbf' SIZE 400M
REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u02/app/oradata01/jlc/sysaux01.dbf' SIZE 400M
REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE 5G
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
'/u02/app/oradata02/jlc/temp01.dbf' SIZE 100M REUSE
UNDO TABLESPACE "UNDOTBS1" DATAFILE
'/u02/app/oradata01/jlc/undotbs1_01.dbf' SIZE 200M REUSE
CHARACTER SET UTF8
LOGFILE GROUP 1 ('/u02/app/oradata01/jlc/redo_1_1a.dbf','/u02/app/oradata02/jlc/redo_1_1b.dbf') SIZE 100M REUSE,
GROUP 2 ('/u02/app/oradata01/jlc/redo_1_2a.dbf','/u02/app/oradata02/jlc/redo_1_2b.dbf') SIZE 100M REUSE;
exit;


sqlplus "/ as sysdba" @create_db_jlc.sql > create_db_jlc.log &

Run Data Dictionary Scripts:

create_dd.sql

@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/rdbms/admin/catclust.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql
exit;


sqlplus "/ as sysdba" @create_dd.sql > create_dd.log &

Notice, this script represents the absolute minimum in terms of data dictionary scripts; add calls to other scripts as necessary.

Cluster enable the database and add additional instances:

add_jlc2.sql

create spfile='/u02/app/oracle/spfilejlc.ora' from pfile;
shutdown immediate;
startup;
alter system set cluster_database=true scope=spfile;
shutdown immediate;
startup;
alter database add logfile thread 2
group 3
('/u02/app/oradata01/jlc/redo_3_3a.dbf',
'/u02/app/oradata02/jlc/redo_3_3b.dbf') size 100M reuse,
group 4
('/u02/app/oradata01/jlc/redo_4_4a.dbf',
'/u02/app/oradata02/jlc/redo_4_4b.dbf') size 100M reuse;
alter database enable public thread 2;
create undo tablespace UNDOTBS2 datafile '/u02/app/oradata02/jlc/undotbs2_01.dbf' SIZE 200M;
exit;

After this, the result spfile's contents should look something like this:

jlc1.__db_cache_size=121634816
jlc2.__db_cache_size=121634816
jlc1.__java_pool_size=4194304
jlc2.__java_pool_size=4194304
jlc1.__large_pool_size=4194304
jlc2.__large_pool_size=4194304
jlc1.__oracle_base='/u03/app/oracle'#ORACLE_BASE set from environment
jlc2.__oracle_base='/u03/app/oracle'#ORACLE_BASE set from environment
jlc1.__pga_aggregate_target=150994944
jlc2.__pga_aggregate_target=150994944
jlc1.__sga_target=226492416
jlc2.__sga_target=226492416
jlc1.__shared_io_pool_size=0
jlc2.__shared_io_pool_size=0
jlc1.__shared_pool_size=88080384
jlc2.__shared_pool_size=88080384
jlc1.__streams_pool_size=0
jlc2.__streams_pool_size=0
*.cluster_database=TRUE
*.compatible='11.1.0'
*.control_files='/u02/app/oradata01/jlc/control01.ctl','/u02/app/oradata02/jlc/c
ontrol02.ctl'
*.db_block_size=8192
*.db_domain='colestock.test'
*.db_name='jlc'
*.diagnostic_dest='/u02/app/oracle'
jlc1.instance_name='jlc1'
jlc2.instance_name='jlc2'
jlc1.instance_number=1
jlc2.instance_number=2
jlc1.local_listener='LISTENER_RAC1'
jlc2.local_listener='LISTENER_RAC2'
*.memory_target=360M
*.remote_listener='LISTENERS_JLC'
*.service_names='jlc.colestock.test'
jlc1.thread=1
jlc2.thread=2
*.undo_management='auto'
jlc1.undo_tablespace='UNDOTBS1'
jlc2.undo_tablespace='UNDOTBS2'


Startup instance(s) added from their associated node(s):

# export ORACLE_SID=jlc2
# . oraenv
# sqlplus "/ as sysdba"
SQL> startup

Update srvctl:

update_srvctl.bsh

#!/bin/bash

srvctl add database -d jlc -o $ORACLE_HOME
srvctl add instance -d jlc -i jlc1 -n rac1
srvctl add instance -d jlc -i jlc2 -n rac2
srvctl enable database -d jlc
srvctl enable instance -d jlc -i jlc1
srvctl enable instance -d jlc -i jlc2


./update_srvctl.bsh

The files from this example are available here: create_rac_db.tar

Labels:

11g New Feature: (DRCP) Database Resident Connection Pooling

Posted at Monday, February 04, 2008
Oracle 11g introduces a Database Server-side Connection pooling feature which holds much promise.

A great overview of this new feature can be found in this excellent article:
Oracle Database 11g:
The Top New Features for DBAs and Developers
by Arup Nanda: Caching and Pooling


I followed Arup's example, and just extended upon it:

Here is the tnsnames.ora entry I created just for connections that are going to use the database resident connection pool (DRCP):

[/u03/app/oracle/product/db/11g/network/admin cubs2@rac2]$ tail -19 tnsnames.ora

CUBS_POOL.COLESTOCK.TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1522))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVER = POOLED)
(SERVICE_NAME = cubs.colestock.test)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)

Of course, I did this on both of my instances.

Next, I cut and pasted Arup's Java class and modified it for my purposes:

[/tmp cubs2@rac2]$ more CacheTest.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class CacheTest {
private String jdbcURL = "jdbc:oracle:oci8:@CUBS_POOL";
private Connection conn = null;
public CacheTest( ) throws ClassNotFoundException {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
public static void main(String[] args) throws ClassNotFoundException, SQLException {
CacheTest check = new CacheTest();
check.dbconnect();
check.doSomething();
}
public void dbconnect() throws SQLException {
System.out.println("Connecting with URL="+jdbcURL+" as scott/tiger");
try {
conn = DriverManager.getConnection( jdbcURL, "scott" , "tiger");
System.out.println("Connected to Database");
} catch (SQLException sqlEx) {
System.out.println(" Error connecting to database : " + sqlEx.toString());
}
}
public void doSomething() throws SQLException {
Statement stmt = null;
ResultSet rset = null;
try {
stmt = conn.createStatement();
System.out.println("Created Statement object");
rset = stmt.executeQuery("select /*+ result_cache */ count(*) from scott.dept");
System.out.println("Retrieved ResultSet object");
if(rset.next())
System.out.println("Result:"+rset.getString(1));
} catch (SQLException sqlEx) {
} finally {
try {
System.out.println("Closing Statment & ResultSet Objects");
if (rset != null) rset.close();
if (stmt != null) stmt.close();
if (conn != null) {
System.out.println("Disconnecting...");
conn.close();
System.out.println("Disconnected from Database");
}
} catch (Exception e) { }
}
}
}

Then I compiled it:

[/tmp cubs2@rac2]$ export ORACLE_SID=cubs2
[/tmp cubs2@rac2]$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11g is /u03/app/oracle
[/tmp cubs2@rac2]$ $ORACLE_HOME/jdk/bin/javac -verbose -classpath .:$ORACLE_HOME/jdbc/lib/jodbc5.jar CacheTest.java
[parsing started CacheTest.java]
[parsing completed 52ms]
[search path for source files: [.]]
[search path for class files: [/u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/jsse.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/jce.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/charsets.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/ext/localedata.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/ext/sunjce_provider.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/ext/sunpkcs11.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/ext/dnsns.jar, .]]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/Connection.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/DriverManager.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/ResultSet.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/SQLException.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/Statement.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Object.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/String.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/ClassNotFoundException.class)]
[checking CacheTest]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Exception.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Throwable.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Class.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/System.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/io/PrintStream.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/io/FilterOutputStream.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/io/OutputStream.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/util/Properties.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Error.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/RuntimeException.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/StringBuilder.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/AbstractStringBuilder.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/CharSequence.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/io/Serializable.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Comparable.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/StringBuffer.class)]
[wrote CacheTest.class]
[total 423ms]

Now, the sample Java Class is ready to go. I created a wrapper script which calls this Class from a bash while loop:

[/tmp cubs2@rac2]$ more run_CacheTest.bsh
#!/bin/bash

export ORACLE_SID=cubs2;
. oraenv ;

while true
do
$ORACLE_HOME/jdk/bin/java -classpath /tmp/:.:$ORACLE_HOME/jdbc/lib/ojdbc5.jar CacheTest
done

Next, we have to start the default connection pool:

SQL> exec dbms_connection_pool.start_pool;

PL/SQL procedure successfully completed.

I placed one on these scripts on each of my database hosts and adjusted the $ORACLE_SID value as appropriate. I will call many instances of this script (using nohup to the background) from each database server to perform a load test; for example:

[/tmp cubs1@rac1]$ nohup /tmp/run_CacheTest.bsh &
[5] 24453
nohup: [/tmp cubs1@rac1]$ appending output to `nohup.out'

The combination of the DRCP as well as the results cache, should allow me to scale well beyond prior limits.

Here are the some of the results:

As you can see, I am simply using the default connection pool:

SQL> select pool_name, inst_id, num_open_servers, num_busy_servers, num_requests, num_waits, historic_max, num_purged from gv$cpool_stats;


POOL_NAME                         INST_ID NUM_OPEN_SERVERS NUM_BUSY_SERVERS NUM_REQUESTS  NUM_WAITS HISTORIC_MAX NUM_PURGED
------------------------------ ---------- ---------------- ---------------- ------------ ---------- ------------ ----------
SYS_DEFAULT_CONNECTION_POOL 1 14 2 2448 0 14 0
SYS_DEFAULT_CONNECTION_POOL 2 10 1 562 0 10 0


Note the high 'Find Count' for each instance:

SQL> select * from gv$result_cache_statistics order by name, inst_id;



INST_ID ID NAME VALUE
---------- ---------- ---------------------------------------- ----------
1 3 Block Count Current 32
2 3 Block Count Current 32
1 2 Block Count Maximum 1728
2 2 Block Count Maximum 1728
1 1 Block Size (Bytes) 1024
2 1 Block Size (Bytes) 1024
1 6 Create Count Failure 0
2 6 Create Count Failure 0
1 5 Create Count Success 1
2 5 Create Count Success 3
1 9 Delete Count Invalid 0
2 9 Delete Count Invalid 0
1 10 Delete Count Valid 0
2 10 Delete Count Valid 0
1 7 Find Count 3426
2 7 Find Count 1292
1 8 Invalidation Count 0
2 8 Invalidation Count 0
1 4 Result Size Maximum (Blocks) 86
2 4 Result Size Maximum (Blocks) 86

As you can see, I was able to perform over a thousand executions on the cluster with virtual no I/O over an 11-minute period:

select * from
2 (select instance_number, executions_total - lag(executions_total,1) over (order by instance_number, snap_id) "EXECUTIONS_TOTAL",snap_id
3 from dba_hist_sqlstat
4 where sql_id='1gy9qq7yjm054')
5 where snap_id=96;


INSTANCE_NUMBER EXECUTIONS_TOTAL    SNAP_ID
--------------- ---------------- ----------
1 662 96
2 529 96

Here are the AWR reports from the test (both instances) - notice the efficiency:

CUBS1 Instance AWR Report
CUBS2 Instance AWR Report


Labels: , ,

RAC: Tuning the Interconnect

Posted at Sunday, February 03, 2008
In order to tune the Interconnect for your RAC cluster, Oracle provides a diagnostic script; you can find this script in the following Metalink Note: 135714.1

In the event that you don't have access to Metalink; here is the script and an example log file from a run against my 2-node, 10.2.0.3 RAC cluster on Enterprise Linux:

racdiag.sql (Rac Diagnostic Script)

Example racdiag.sql log file

In regards to how the Interconnect is performing, the aforementioned script runs a couple of key queries; here are a couple of the more important ones:

-- GLOBAL CACHE CR PERFORMANCE
-- This shows the average latency of a consistent block request.
-- AVG CR BLOCK RECEIVE TIME should typically be about 15 milliseconds depending
-- on your system configuration and volume, is the average latency of a
-- consistent-read request round-trip from the requesting instance to the holding
-- instance and back to the requesting instance. If your CPU has limited idle time
-- and your system typically processes long-running queries, then the latency may
-- be higher. However, it is possible to have an average latency of less than one
-- millisecond with User-mode IPC. Latency can be influenced by a high value for
-- the DB_MULTI_BLOCK_READ_COUNT parameter. This is because a requesting process
-- can issue more than one request for a block depending on the setting of this
-- parameter. Correspondingly, the requesting process may wait longer. Also check
-- interconnect badwidth, OS tcp settings, and OS udp settings if
-- AVG CR BLOCK RECEIVE TIME is high.
--
set numwidth 20
column "AVG CR BLOCK RECEIVE TIME (ms)" format 9999999.9
select b1.inst_id, b2.value "GCS CR BLOCKS RECEIVED",
b1.value "GCS CR BLOCK RECEIVE TIME",
((b1.value / b2.value) * 10) "AVG CR BLOCK RECEIVE TIME (ms)"
from gv$sysstat b1, gv$sysstat b2
where b1.name = 'global cache cr block receive time' and
b2.name = 'global cache cr blocks received' and b1.inst_id = b2.inst_id
or b1.name = 'gc cr block receive time' and
b2.name = 'gc cr blocks received' and b1.inst_id = b2.inst_id ;


-- GLOBAL CACHE LOCK PERFORMANCE
-- This shows the average global enqueue get time.
-- Typically AVG GLOBAL LOCK GET TIME should be 20-30 milliseconds. the elapsed
-- time for a get includes the allocation and initialization of a new global
-- enqueue. If the average global enqueue get (global cache get time) or average
-- global enqueue conversion times are excessive, then your system may be
-- experiencing timeouts. See the 'WAITING SESSIONS', 'GES LOCK BLOCKERS',
-- 'GES LOCK WAITERS', and 'TOP 10 WAIT EVENTS ON SYSTEM' sections if the
-- AVG GLOBAL LOCK GET TIME is high.
--
set numwidth 20
column "AVG GLOBAL LOCK GET TIME (ms)" format 9999999.9
select b1.inst_id, (b1.value + b2.value) "GLOBAL LOCK GETS",
b3.value "GLOBAL LOCK GET TIME",
(b3.value / (b1.value + b2.value) * 10) "AVG GLOBAL LOCK GET TIME (ms)"
from gv$sysstat b1, gv$sysstat b2, gv$sysstat b3
where b1.name = 'global lock sync gets' and
b2.name = 'global lock async gets' and b3.name = 'global lock get time'
and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id
or b1.name = 'global enqueue gets sync' and
b2.name = 'global enqueue gets async' and b3.name = 'global enqueue get time'
and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id;


-- DLM TRAFFIC INFORMATION
-- This section shows how many tickets are available in the DLM. If the
-- TCKT_WAIT columns says "YES" then we have run out of DLM tickets which could
-- cause a DLM hang. Make sure that you also have enough TCKT_AVAIL.
--
set numwidth 5
select * from gv$dlm_traffic_controller
order by TCKT_AVAIL;


Oracle also maintains a RAC survival guide - which is quite helpful - here: 203226.1

Labels: , ,

Upgrade Notes from 10g RAC to 11g

Posted at Sunday, September 30, 2007
Here is how I went about upgrading the aforementioned, 2-node 10g RAC cluster

Here are the basic steps I followed:

Download, mount, and unzip the 11g Database and Clusterware Software on the original installation node.

I ran the Clusterware preupdate.sh script on both nodes, since I don't have a shared clusterware installation:

root@rac2 # /u03/clusterware/upgrade/preupdate.sh -crshome /u03/app/oracle/product/crs/10.2 -crsuser oracle -shutdown

The aforementioned, unlocks and stops the 10g CRS Software in preparation for installation. The new software must be installed in the same directory as the existing CRS software.

Next, install the 11g Clusterware Software:

oracle@rac1 upgrade]$ echo $ORA_CRS_HOME
/u03/app/oracle/product/crs/10.2
[oracle@rac1 upgrade]$ export DISPLAY=192.168.1.4:0.0
[oracle@rac1 upgrade]$ /u03/clusterware/runInstaller

Follow the Universal Installer prompts to install on both nodes:

Next
> Next (ORA_CRS_HOME should be already selected)
> Next (Select all applicable nodes)
> Next Product Checks
> Install (after verifying)

# When prompted run root on both nodes:

[root@rac1 ~]# /u03/app/oracle/product/crs/10.2/install/rootupgrade

Here is the output from the first node:

Checking to see if Oracle CRS stack is already up...

copying ONS config file to 11.1 CRS home
/bin/cp: `/u03/app/oracle/product/crs/10.2/opmn/conf/ons.config' and `/u03/app/oracle/product/crs/10.2/opmn/conf/ons.config' are the same file
/u03/app/oracle/product/crs/10.2/opmn/conf/ons.config was copied successfully to
/u03/app/oracle/product/crs/10.2/opmn/conf/ons.config
WARNING: directory '/u03/app/oracle/product/crs' is not owned by root
WARNING: directory '/u03/app/oracle/product' is not owned by root
WARNING: directory '/u03/app/oracle' is not owned by root
WARNING: directory '/u03/app' is not owned by root
WARNING: directory '/u03' is not owned by root
Oracle Cluster Registry configuration upgraded successfully
Adding daemons to inittab
Attempting to start Oracle Clusterware stack
Waiting for Cluster Synchronization Services daemon to start
Waiting for Cluster Synchronization Services daemon to start
Waiting for Cluster Synchronization Services daemon to start
Waiting for Cluster Synchronization Services daemon to start
Cluster Synchronization Services daemon has started
Event Manager daemon has started
Cluster Ready Services daemon has started
Oracle CRS stack is running under init(1M)
clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 1: rac1 rac1-priv rac1
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
clscfg -upgrade completed successfully
CRS stack on this node, is successfully
upgraded to 11.1.0.6.0
Checking the existence of nodeapps on this node
Creating '/u03/app/oracle/product/crs/10.2/install/paramfile.crs'
with data used for CRS configuration
Setting CRS configuration values in
/u03/app/oracle/product/crs/10.2/install/paramfile.crs

Here is the output from the remote node:

root@rac2 ~]# /u03/app/oracle/product/crs/10.2/install/rootupgrade
Checking to see if Oracle CRS stack is already up...

copying ONS config file to 11.1 CRS home
/bin/cp: `/u03/app/oracle/product/crs/10.2/opmn/conf/ons.config' and
`/u03/app/oracle/product/crs/10.2/opmn/conf/ons.config' are the same file
/u03/app/oracle/product/crs/10.2/opmn/conf/ons.config was copied successfully to
/u03/app/oracle/product/crs/10.2/opmn/conf/ons.config
WARNING: directory '/u03/app/oracle/product/crs' is not owned by root
WARNING: directory '/u03/app/oracle/product' is not owned by root
WARNING: directory '/u03/app/oracle' is not owned by root
WARNING: directory '/u03/app' is not owned by root
WARNING: directory '/u03' is not owned by root
Oracle Cluster Registry configuration upgraded successfully
Adding daemons to inittab
Attempting to start Oracle Clusterware stack
Waiting for Cluster Synchronization Services daemon to start
Waiting for Cluster Synchronization Services daemon to start
Waiting for Cluster Synchronization Services daemon to start
Waiting for Cluster Synchronization Services daemon to start
Cluster Synchronization Services daemon has started
Event Manager daemon has started
Cluster Ready Services daemon has started
Oracle CRS stack is running under init(1M)
clscfg: EXISTING configuration version 4 detected.
clscfg: version 4 is 11 Release 1.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 2: rac2 rac2-priv rac2
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
clscfg -upgrade completed successfully
CRS stack on this node, is successfully
upgraded to 11.1.0.6.0
Checking the existence of nodeapps on this node
Creating '/u03/app/oracle/product/crs/10.2/install/paramfile.crs' with data used for CRS configuration
Setting CRS configuration values in /u03/app/oracle/product/crs/10.2/install/paramfile.crs

After running the root scripts press 'OK' in the pop-up window, The Installer will run the Cluster Verification Utility. Once that runs successfully, press exit.

Now install the Database Software onto both nodes, afterwards, I will upgrade the database:

First, add a new entry to the oratab for your 11g Installation:

echo "11g:/u03/app/oracle/product/db/11g:N" >> /etc/oratab
[oracle@rac1 ~]$ export ORACLE_SID=11g
[oracle@rac1 ~]$ . oraenv
[oracle@rac1 ~]$ /u03/database/runInstaller

Follow the Universal Installer prompts to install on both nodes:

Next
> Choose Edition (ostensibly, Enterprise), Next
> Specify the Name and the path of the new $ORACLE_HOME, Next
> Specify the nodes for installation, Next
> Prerequisites, Next
> Select 'No' for Upgrade Database, Next
> Choose Install Software Only, Next
> Assign O/S groups to Operations, Next
> Install

Run the root.sh script on both nodes:

[root@rac1 crs] # /u03/app/oracle/product/db/11g/root.sh

Running Oracle 11g root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u03/app/oracle/product/db/11g

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

After running the root scripts press 'OK' in the pop-up window. The Installer is now complete, press exit.

Next, I upgrade the listeners that runs out of the 10.2.0.3 homes to the new 11g $ORACLE_HOMEs - do this on both nodes:

[oracle@rac1 10.2]$ export ORACLE_SID=cubs1
[oracle@rac1 10.2]$ . oraenv
[oracle@rac1 10.2]$ cd $ORACLE_HOME/network/admin
[oracle@rac1 admin]$ ls -lart
total 52
-rw-r--r-- 1 oracle dba 172 Dec 26 2003 shrept.lst
drwxr-x--- 2 oracle dba 4096 Sep 21 19:40 samples
drwxr-x--- 11 oracle dba 4096 Sep 21 19:44 ..
-rw-r--r-- 1 oracle dba 574 Sep 25 15:54 listener.ora
-rw-r--r-- 1 oracle dba 230 Sep 28 15:11 ldap.ora
-rw-r--r-- 1 oracle dba 16776 Sep 28 15:29 sqlnet.log
-rw-r--r-- 1 oracle dba 3786 Sep 28 16:51 tnsnames.ora
-rw-r--r-- 1 oracle dba 218 Sep 28 16:52 sqlnet.ora
drwxr-x--- 3 oracle dba 4096 Sep 28 16:52 .
[oracle@rac1 admin]$ lsnrctl LISTENER_RAC1 stop
[oracle@rac1 admin]$ mv listener.ora /u03/app/oracle/product/db/11g/network/admin/.
[oracle@rac1 admin]$ cp tnsnames.ora sqlnet.ora ldap.ora
/u03/app/oracle/product/db/11g/network/admin/.
[oracle@rac1 admin]$ ps -ef grep tns grep -v grep awk '{print $2}' xargs kill
[oracle@rac1 admin]$ export ORACLE_SID=11g
[oracle@rac1 admin]$ . oraenv
[oracle@rac1 admin]$ lsnrctl start LISTENER_RAC1


Now that the listener has been moved to the highest version $ORACLE_HOME on the machine, I am ready to upgrade a 10.2.0.3 clustered database to 11g (11.1.0.6.0).

First, I will run the Pre-Upgrade Information Tool:

[oracle@rac1 admin]$ cp
/u03/app/oracle/product/db/11g/rdbms/admin/utlu111i.sql
/tmp
[oracle@rac1 admin]$ cd /tmp
[oracle@rac1 tmp]$ export ORACLE_SID=cubs1
[oracle@rac1 tmp]$ . oraenv
[oracle@rac1 tmp]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Sep 30 17:23:09 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> spool upgrade_info.log
SQL> @/tmp/utlu111i.sql
SQL> spool off;


After examining the upgrade log, I have to perform the following tasks:

  • Upgrade sga_target parameter to 336MB

  • Replace the deprecated *_dest parameters with the new all inclusive one, diagnostic_dest

  • Patch - via opatch - the 10.2.0.3 databases and $ORACLE_HOMEs to use Version 4 of the timezone file; this is patch number: 5632264. You can verify correct application via v$timezone_file performance view. Remember that during the one-off patch as well as the upgrade to 11g the RAC database must have cluster_database parameter set to FALSE so that the database can be mounted exclusive.
The next step is to migrate the password file and modified init.ora.

On the first node:

[oracle@rac1 tmp]$ export ORACLE_SID=11g
[oracle@rac1 tmp]$ . oraenv
[oracle@rac1 tmp]$ echo $ORACLE_HOME
/u03/app/oracle/product/db/11g
[oracle@rac1 tmp]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
[oracle@rac1 tmp]$ export ORACLE_SID=cubs1
[oracle@rac1 tmp]$ echo $PATH
/u03/app/oracle/product/db/11g/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/oracle/bin
[oracle@rac1 tmp]$ cd $ORACLE_HOME/dbs
[oracle@rac1 dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 1 15:09:34 2007

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

Connected to an idle instance.

SQL> create spfile='/u02/app/oradata2/spfile/spfilecubs.ora' from pfile='/tmp/initcubs.ora';

File created.

SQL> exit
Disconnected
[oracle@rac1 dbs]$ ln -s /u02/app/oradata/dbs/orapwcubs orapwcubs2
[oracle@rac1 dbs]$ ln -s /u02/app/oradata2/spfile/spfilecubs.ora spfilecubs1.ora

On the second node:

[oracle@rac2 dbs]$ export ORACLE_SID=11g
[oracle@rac2 dbs]$ . oraenv
[oracle@rac2 dbs]$ cd $ORACLE_HOME/dbs
[oracle@rac2 dbs]$ ln -s /u02/app/oradata/dbs/orapwcubs orapwcubs2
[oracle@rac2 dbs]$ ln -s /u02/app/oradata2/spfile/spfilecubs.ora spfilecubs2.ora

Next upgrade the database by running the appropriate scripts; on the primary node:

[oracle@rac1 dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 1 15:16:49 2007

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

Connected to an idle instance.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 351522816 bytes
Fixed Size 1299876 bytes
Variable Size 155191900 bytes
Database Buffers 188743680 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

Once the database has been upgraded, the database will automatically be shutdown by the previous script. Startup the database and run the post scripts that don't require exclusive access to the database and verify that there are no invalid objects:

[oracle@rac1 dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 1 16:15:08 2007

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 351522816 bytes
Fixed Size 1299876 bytes
Variable Size 184552028 bytes
Database Buffers 159383552 bytes
Redo Buffers 6287360 bytes
SQL> alter system set cluster_database=TRUE scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 351522816 bytes
Fixed Size 1299876 bytes
Variable Size 184552028 bytes
Database Buffers 159383552 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.

SQL> @$ORACLE_HOME/rdbms/admin/utlu111s.sql
SQL> @$ORACLE_HOME/rdbms/admin/catuppst.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Update the oratab on both nodes and re-source the environment via oratab.

On the first node:

[oracle@rac1 dbs]$ vi /etc/oratab
[oracle@rac1 dbs]$ grep cubs1 /etc/oratab
cubs1:/u03/app/oracle/product/db/11g:N
[oracle@rac1 dbs]$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11g is /u03/app/oracle
[oracle@rac1 dbs]$ echo $PATH
/u03/app/oracle/product/db/11g/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/oracle/bin

On the second node:

[oracle@rac2 dbs]$ vi /etc/oratab
[oracle@rac2 dbs]$ grep cubs2 /etc/oratab
cubs2:/u03/app/oracle/product/db/11g:N
[oracle@rac2 dbs]$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11g is /u03/app/oracle
[oracle@rac2 dbs]$ echo $PATH
/u03/app/oracle/product/db/11g/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/oracle/bin

Upgrade the Oracle Cluster Registry (OCR) to reflect the upgraded nature of the database, including the previously migrated listeners:

[oracle@rac1 bin]$ export ORACLE_SID=10.2.0.3
[oracle@rac1 bin]$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/10.2 is /u03/app/oracle
[oracle@rac1 bin]$ srvctl remove database -d cubs
Remove the database cubs? (y/[n]) y
[oracle@rac1 bin]$ export ORACLE_SID=11g
[oracle@rac1 bin]$ . oraenv
[oracle@rac1 bin]$ srvctl add database -d cubs -o /u03/app/oracle/product/db/11g
[oracle@rac1 bin]$ srvctl add instance -d cubs -i cubs1 -n rac1
[oracle@rac1 bin]$ srvctl add instance -d cubs -i cubs2 -n rac2
[oracle@rac1 bin]$ srvctl modify listener -n rac1 -l LISTENER_RAC1 -o /u03/app/oracle/product/db/11g
[oracle@rac1 bin]$ srvctl modify listener -n rac2 -l LISTENER_RAC2 -o /u03/app/oracle/product/db/11g

Next I will upgrade the compatibility of the database and cycle the database in order to verify:

[oracle@rac1 bin]$ export ORACLE_SID=cubs1
[oracle@rac1 bin]$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11g is /u03/app/oracle
[oracle@rac1 bin]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 1 16:51:17 2007

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> alter system set compatible='11.0.0' scope=spfile;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@rac1 bin]$ srvctl stop database -d cubs
[oracle@rac1 bin]$ srvctl start database -d cubs
[oracle@rac1 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 1 18:03:20 2007

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.0.0

If applicable, upgrade your EM Agents to reflect the software changes (both nodes):

$ export ORACLE_SID=agent10g
[oracle@rac2 ~]$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/agent10g is /u03/app/oracle
[oracle@rac2 ~]$ agentca -d

Stopping the agent using /u03/app/oracle/product/agent10g/bin/emctl stop agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.
Running agentca using /u03/app/oracle/product/agent10g/oui/bin/runConfig.sh ORACLE_HOME=/u03/app/oracle/product/agent10g ACTION=Configure MODE=Perform RESPONSE_FILE=/u03/app/oracle/product/agent10g/response_file RERUN=TRUE INV_PTR_LOC=/etc/oraInst.loc COMPONENT_XML={oracle.sysman.top.agent.10_2_0_1_0.xml}
Perform - mode is starting for action: Configure


Perform - mode finished for action: Configure

You can see the log file: /u03/app/oracle/product/agent10g/cfgtoollogs/oui/configActions2007-10-01_06-05-43-PM.log

One of my last steps is to take a backup; therefore, I will need to upgrade my RMAN catalog and/or create a new one. I have opted to create a new one, so that the other databases that I backup are not disturbed. First I create a new rman database user for my repository database:

DROP USER RMAN_11g CASCADE;
CREATE USER RMAN_11g
IDENTIFIED BY "rman"
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 1 Role for RMAN
GRANT RECOVERY_CATALOG_OWNER TO RMAN_11g;
ALTER USER RMAN DEFAULT ROLE ALL;
-- 1 System Privilege for RMAN
GRANT CREATE SESSION TO RMAN_11g;
-- 1 Tablespace Quota for RMAN
ALTER USER RMAN_11G QUOTA UNLIMITED ON USERS;
DROP USER RMAN_11g CASCADE
Error at line 1
ORA-01918: user 'RMAN_11G' does not exist

User created.
Grant complete.
User altered.
Grant complete.
User altered.

Now that I have an RMAN catalog owner on the repository database, it is time for me to create a new catalog, register my 11g RAC database and add back my customizations:

[oracle@rac2 ~]$ export ORACLE_SID=cubs2
[oracle@rac2 ~]$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11g is /u03/app/oracle
[oracle@rac2 ~]$ rman target=/ catalog=rman_11g/rman@rman

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Oct 2 13:39:46 2007

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

connected to target database: CUBS (DBID=2121269038)
connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> @/home/oracle/bin/backup_scripts.rman

RMAN> replace script 'cubs_full_backup' {
2> sql "alter session set optimizer_mode=RULE";
3> allocate channel ch device type disk format '/u03/app/oracle/orabackup/cubs_%U.rman';
4> backup full database plus archivelog delete input;
5> }
replaced script cubs_full_backup

RMAN>
RMAN> replace script 'cleanup_catalog' {
2> sql "alter session set optimizer_mode=RULE";
3> allocate channel ch device type disk;
4> crosscheck backup;
5> delete noprompt expired backup;
6> }
replaced script cleanup_catalog

RMAN> **end-of-file**


Afterwards, I perform a backup using one of my stored scripts:

RMAN> run { execute script cubs_full_backup; }

executing script: cubs_full_backup

sql statement: alter session set optimizer_mode=RULE

allocated channel: ch
channel ch: SID=103 instance=cubs2 device type=DISK


Starting backup at 02-OCT-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=108 RECID=241 STAMP=634918386
channel ORA_DISK_1: starting piece 1 at 02-OCT-07
channel ORA_DISK_1: finished piece 1 at 02-OCT-07
piece handle=/u03/1qitg5fm_1_1 tag=TAG20071002T141310 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/app/oradata/cubs/arch/cubs_2_108_633910001.arc RECID=241 STAMP=634918386
Finished backup at 02-OCT-07

Starting backup at 02-OCT-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/app/oradata/cubs/system01.dbf
input datafile file number=00003 name=/u02/app/oradata/cubs/sysaux01.dbf
input datafile file number=00002 name=/u02/app/oradata/cubs/undotbs01.dbf
input datafile file number=00005 name=/u02/app/oradata/cubs/undotbs02.dbf
input datafile file number=00004 name=/u02/app/oradata/cubs/users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-OCT-07
channel ORA_DISK_1: finished piece 1 at 02-OCT-07
piece handle=/u03/1ritg5fo_1_1 tag=TAG20071002T141311 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 02-OCT-07
channel ORA_DISK_1: finished piece 1 at 02-OCT-07
piece handle=/u03/1sitg5hf_1_1 tag=TAG20071002T141311 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-OCT-07

Starting backup at 02-OCT-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=109 RECID=242 STAMP=634918451
channel ORA_DISK_1: starting piece 1 at 02-OCT-07
channel ORA_DISK_1: finished piece 1 at 02-OCT-07
piece handle=/u03/1titg5hl_1_1 tag=TAG20071002T141413 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/app/oradata/cubs/arch/cubs_2_109_633910001.arc RECID=242 STAMP=634918451
Finished backup at 02-OCT-07

On my test machine, I have a lot of products installed and simply export the TNS_ADMIN variable instead of doing a lot of symbolic linking and/or network file maintenanace. In this situation, I perform the following command for any CRS resource database that I still want to run out of the old 10.2.0.3 HOME (for more information see Metalink Note 360575.1):

srvctl setenv database -d rman -t TNS_ADMIN=/u03/app/oracle/product/db/11g/network/admin


Here is a recent post on OTN that deals with just this topic: Upgrading to Oracle RAC 11g on Oracle Enterprise Linux

Labels: ,

How to Duplicate a RAC database using RMAN

Posted at Monday, September 24, 2007
Duplicating a RAC database via RMAN can be a bit tricky. In essence, it is the same as duplicating a single instance to a single instance with a couple of twists.

In my example, I am cloning a 2 instance 10gR2 (10.2.0.3) database to another 10gR2(10.2.0.3) on the same server. In reality, I am cloning a template database that will become my RMAN repository. Therefore, in this example, I will be using a disk backup without an RMAN repository. In order to duplicate a RAC database (RAC to RAC), you need to follow these basic steps:

1.) Create a password file for the new database you wish to create (the auxiliary)

2.) Create a new parameter file for your new database and REMOVE all RAC-centric (multiple-instance) parameters. In effect, you are duplicating to a single instance and then make it a clustered database afterwards.

3.) Create a static listener entry for your new database and reload the listener as appropriate. This step is necessary, because an RMAN duplicate uses an auxiliary database that is in no mount and therefore will not automatically register itself with the listener(s) as specified by local_listener, remote_listener and or the default, 1521.

For example:

      (SID_DESC =   
(GLOBAL_DBNAME = rman.colestock.test)
(ORACLE_HOME = /u03/app/oracle/product/db/10.2)
(SID_NAME = rman2)
)

4.) Connect to the auxiliary database and put it in no mount

5.) Change the RMAN configuration of the target database to reflect a location for a disk backup:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u03/%U';

and then take a full backup - including archivelogs - via:

backup database plus archivelog;

The DUPLICATE operation uses this backup, including a connection to the TARGET database in order to create the auxiliary

6.) Create an RMAN script file - similar to the following - that renames files as appropriate:

run {
allocate auxiliary channel ch1 device type disk format '/u03/%U';
set newname for tempfile 1 to '/u02/app/oradata/rman/temp01.dbf';
configure auxname for datafile 1 to '/u02/app/oradata/rman/system01.dbf';
configure auxname for datafile 2 to '/u02/app/oradata/rman/undotbs01.dbf';
configure auxname for datafile 3 to '/u02/app/oradata/rman/sysaux01.dbf';
configure auxname for datafile 4 to '/u02/app/oradata/rman/users01.dbf';
configure auxname for datafile 5 to '/u02/app/oradata/rman/undotbs02.dbf';
duplicate target database to "rman"
logfile
group 1 ('/u02/app/oradata/rman/redo01a.log',
'/u02/app/oradata2/rman/redo01b.log') size 50M reuse,
group 2 ('/u02/app/oradata/rman/redo02a.log',
'/u02/app/oradata2/rman/redo02b.log') size 50M reuse;
}

Notice how the auxiliary channel points to where the disk backup is.

7.) In order to avoid 'Internal Bug 4355382 ORA-38856: FAILED TO OPEN DATABASE WITH RESETLOGS WHEN USING RAC BACKUP,' set the following initialization parameter on the auxiliary; shutdown immediate; and then put the database back into no mount:

_no_recovery_through_resetlogs=TRUE

8.) Run the aforementioned script to duplicate the database:

rman target=/ auxiliary=sys/password@rman2

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Sep 24 14:40:29 2007

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

connected to target database: CUBS (DBID=2121269038)
connected to auxiliary database: RMAN (not mounted)

RMAN> @duplicate_cubs.rman

9.) If Step 8 is successful, then turn the auxiliary database into a RAC database by adding back all the necessary parameters, such as cluster_database, cluster_instances, thread, local_listener, etc.

10.) Add the second thread of online redo logs and enable that thread; startup the second instance:

SQL> alter database add logfile thread 2 group 3 ('/u02/app/oradata/rman/redo03a.log',
'/u02/app/oradata2/rman/redo03b.log') size 50m reuse;

Database altered.

SQL> alter database add logfile thread 2 group 4 ('/u02/app/oradata/rman/redo04a.log',
'/u02/app/oradata2/rman/redo04b.log') size 50m reuse;

SQL> alter database enable public thread 2;

Database altered.

SQL> startup;

11.) Optionally, add the new database to srvctl:

srvctl add database -d rman -o /u03/app/oracle/product/db/10.2
srvctl add instance -d rman -i rman2 -n rac2
srvctl add instance -d rman -i rman1 -n rac1


Labels: , ,

Build your own RAC (10gR2/iSCSI/Linux)

Posted at Monday, September 24, 2007
I have had a lot of homemade Oracle RAC installations over the years. In my latest build, I decided to try the iSCSI route suggested in Jeffrey Hunter's latest Build Your Own RAC (iSCSI) article.

In the past, I had used the ever popular dual-login firewire drive. I was hoping that this time, the use of a NAS-like appliance and a newer version of Oracle's Clustered File System would result in a more stable configuration. This configuration has turned out, in fact, to be remarkably stable - orders of magnitude more so than any prior combination I have employed.

In terms of cost, I was able to keep the price tab under 2K. In terms of hardware, I used exclusively refurbished Dell Optiplex gear (a great value): 2-GX520(P4-3000) desktops w/ 2GB RAM (for the RAC nodes) and 1-GX745(Core Duo 1860) desktop w/ 2GB RAM for the Openfiler appliance. I used exclusively Intel Gigabit, low-profile NICs as well as 2-Netgear Gigabit 5-port switches.

Here are front and rear pictures of my RAC:




In terms of software, I used Oracle's Enterprise Linux distribution Release 4 Update 5, which required no additional packages and/or updates, besides the OCFS packages described in Hunter's article.

I deviated from the aforementioned article in the following ways:

  • I don't particularly enjoy ASM and or using the ASMLib to handle the storage. For a home RAC, I think using a clustered file system - in this case OCFS2 - is more appropriate, so the entire database is on filesystems mounted under the ocfs2 type.

  • On the Openfiler server, I installed a large drive and simply carved it up, using it for the Volume/Volume Groups mounted by the RAC nodes.

In my opinion, this is a must-have for any career Oracle DBA.

Here are the final configuration files from each node:

rac1.tar | rac2.tar

For example:

# tar -tf rac2.tar
etc/resolv.conf
etc/sysctl.conf
home/oracle/.bash_profile
etc/oratab
etc/iscsi-map.sh
etc/fstab
etc/profile
etc/csh.login
etc/rc.d/rc.local
etc/oraInst.loc
etc/hosts.equiv
u02/oracle/ora1/em/spfileem.ora
u02/oracle/ora1/em/orapwem
u02/oracle/ora1/em/tnsnames.ora
u02/oracle/ora1/em/listener.ora.rac1
u02/oracle/ora1/em/listener.ora.rac2
etc/iscsi.conf
u02/oracle/logs/em/scripts/cloneDBCreation.sql
u02/oracle/logs/em/scripts/CloneRmanRestore.sql
u02/oracle/logs/em/scripts/CreateClustDBViews.sql
u02/oracle/logs/em/scripts/em1.sql
u02/oracle/logs/em/scripts/postDBCreation.sql
u02/oracle/logs/em/scripts/postScripts.sql
u02/oracle/logs/em/scripts/rmanRestoreDatafiles.sql
u02/oracle/logs/em/scripts/initemTemp.ora
u02/oracle/logs/em/scripts/init.ora
u02/oracle/logs/em/scripts/em1.sh


Labels: