Rman, incrementally updated backups, switch to copy

~ rajeevramdas@aol.com ~ Leave a comment

Some oracle customers use SAN based technologies to keep a backup copy of the database in sync (Think EMC Timefinder and BCV’s). The typical backup strategy tends to be, to keep a full copy of the database, and on a daily basis to update this backup copy of the database (Typically a BCV synchronize  operation), with only the incremental changes that have happened at the disk level.

In case off a loss of file system in the primary disks, they can switch the database to run from another copy which is maintained as a backup by the Storage subsystem.

A similar strategy can be implemented for  oracle databases, using RMAN incrementally updated backups (Without requiring any expensive SAN level software for maintaining these copies). These backup copies can be updated on a daily basis using just the incremental changes in the database. In case of a loss in the database, you can switch to this copy to be used as the database. You can also generate clones from these backups using the rman duplicate command.

At a high level the steps involved are

  • Configure the database to run in archivelog mode
  • Configure block change tracking at the database level
  • Create a level 0 full image copy of the database using rman
  • Create level 1 incremental backups of the database and update the level 0 copy with this incremental backup
  • In case of need, use the rman switch command to switch the database to this incrementally updated backup.

Below is an example of how this is done.

Enable Archivelog and Block Change Tracking

sqlplus / as sysdba
startup mount;
alter database archivelog;
alter system set log_archive_dest_1=’LOCATION=/u01/orarch/rk01′ scope=spfile;
alter database open;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE ‘/u01/orarch/rk01/rman/rman_change_track.f’ REUSE;

Run a level 0 full image copy backup of the database.

A sample script to run the full image copy backup is below
#!/bin/bash
rman target=”sys/manager@rk01″ log=/u01/oraback/fullcopy.log << EOF
RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT ‘/u01/oraback/%b’;
BACKUP INCREMENTAL LEVEL 0 as copy DATABASE TAG ‘fullcopy’;
RELEASE CHANNEL ch1;
}
RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT ‘/u01/oraback/%U’;
BACKUP as copy ARCHIVELOG ALL;
BACKUP as copy CURRENT CONTROLFILE;
RELEASE CHANNEL ch1;
}
EXIT;
EOF
In the above script there are two distinct rman scripts.
The first one backs up the database and the second one backs up the control file and archivelogs.
I am using the %b format string for the database backup to keep the database file names intact (In preparation for a switch) (You dont have to keep the datafile names the same, just makes it easy to understand).
You cannot do controlfile and archivelog backups using the %b format string. This is why i have two separate scripts.
I tag the backup with a name ‘fullcopy’, which later helps us with applying the incremental backup to this tag.

Now perform some updates in the database. (This simulates database changes, after which we can run an incremental backup).

#!/bin/bash
sqlplus hr/hr@rk01 <<EOC
set echo on;
select employee_id,first_name,last_name from
employees
where
employee_id = 194
/
update employees set last_name=’Adams’
where
employee_id=194
/
select employee_id,first_name,last_name from
employees
where
employee_id = 194
/
EOC
I updated the last_name for the employee number 194.

Now perform an incremental level 1 backup of the database

#!/bin/bash
rman target=”sys/manager@rk01″ log=/u01/oraback/incr.log << EOF
RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT ‘/u01/oraback/%U’;
BACKUP
INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG ‘fullcopy’
DATABASE;
RELEASE CHANNEL ch1;
}
EXIT;
EOF

Now Apply the incremental backup to the original level 0 image copy

#!/bin/bash
rman target=”sys/manager@rk01″ log=/u01/oraback/incrapply.log << EOF
RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT ‘/u01/oraback/%U’;
RECOVER COPY OF DATABASE WITH TAG ‘fullcopy’;
RELEASE CHANNEL ch1;
}
EXIT;
EOF
Run the following commands to capture the location of the datafiles for the database
SQL> select file_name from dba_data_files;
FILE_NAME
——————————————————————————–
/u01/oradata/rk01/users01.dbf
/u01/oradata/rk01/undotbs01.dbf
/u01/oradata/rk01/sysaux01.dbf
/u01/oradata/rk01/system01.dbf
/u01/oradata/rk01/example01.dbf

Now switch the database to run from the copy

Shutdown and startup and mount the database; (Do not open the database)
oracle@rramads-us2:~$ rman target /
Recovery Manager: Release 11.2.0.2.0 – Production on Sat Nov 6 17:44:40 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: RK01 (DBID=2035601811, not open)
RMAN> switch database to copy;
using target database control file instead of recovery catalog
datafile 1 switched to datafile copy “/u01/oraback/system01.dbf”
datafile 2 switched to datafile copy “/u01/oraback/sysaux01.dbf”
datafile 3 switched to datafile copy “/u01/oraback/undotbs01.dbf”
datafile 4 switched to datafile copy “/u01/oraback/users01.dbf”
datafile 5 switched to datafile copy “/u01/oraback/example01.dbf”
RMAN> recover database;
Starting recover at 06-NOV-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oraback/system01.dbf
destination for restore of datafile 00002: /u01/oraback/sysaux01.dbf
destination for restore of datafile 00003: /u01/oraback/undotbs01.dbf
destination for restore of datafile 00004: /u01/oraback/users01.dbf
destination for restore of datafile 00005: /u01/oraback/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oraback/14lsbd4b_1_1
channel ORA_DISK_1: piece handle=/u01/oraback/14lsbd4b_1_1 tag=FULLCOPY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 06-NOV-10
RMAN> alter database open;
database opened
Now if you query the file_names they will point to the /u01/oraback directory

SQL> select file_name from dba_data_files;
FILE_NAME
——————————————————————————–
/u01/oraback/users01.dbf
/u01/oraback/undotbs01.dbf
/u01/oraback/sysaux01.dbf
/u01/oraback/system01.dbf
/u01/oraback/example01.dbf
Check if the changes we made to the hr.employees table are still there (To ensure that the incremental backup did work).
SQL> select last_name from hr.employees where employee_id = 194;
LAST_NAME
————————-
Adams

Copying database files from one asm instance to another using asmcmd

~ rajeevramdas@aol.com ~ Leave a comment

Often one might have to copy datafiles from one database instance to another (And hence one ASM instance to another), for eg: when copying datafiles from prod to development.

You can use the cp command in asmcmd to accomplish this. cp can copy files from one instance to another. You can specify the username/password@connecstring for the target asm instance in the cp command.

ASMCMD [+] > cp +data/orcl/datafile/EXAMPLE.265.691577295 sys/password@myserver.+ASM:/scratch/backup/myexamples.bak

Exadata cloning using sun ZFS storage appliance snapshots

~ rajeevramdas@aol.com ~ Leave a comment

Oracle Customers often prefer, the ease of use of using system snapshots (Think Emc clarion, Snapview, Snapshots), for cloning databases. Oracle Sun Zfs storage Appliance has infiniband connectivity and storage snapshot capabilities that can be leveraged to efficiently create database clones.

Oracle has a new white paper that outlines the steps  and best practices to use Dataguard in conjunction with the Zfs storage appliance to create database clones using snapshotting technology. This method can also be used with the oracle exadata database machine, keeping in mind a couple of ceavats that are outlined in the white paper.

The white paper can be found at http://www.oracle.com/technetwork/database/features/availability/maa-db-clone-szfssa-172997.pdf

Installing cx-oracle with 11.2.0.2 on ubuntu

~ rajeevramdas@aol.com ~ Leave a comment

cx_Oracle is a Python extension module that allows access to Oracle databases and conforms to the Python database API specification. Below are the steps i used to setup cx_Oracle, with 11.2.0.2, python 2.6 on Ubuntu 10.04 (Lucid Lynx).

1) Installed Oracle 11.2.0.2 Enterprise Edition on Ubuntu (You can also configure this by just installing the 11.2.0.2 instant client too)

2) Download cx_Oracle Source code from http://cx-oracle.sourceforge.net/

3) Install python-dev on ubuntu (Or else you will get compile errors (Like file Python.h not found) when you try to install cx-oracle)

– sudo apt-get install python-dev

4) Login as root

5) export ORACLE_HOME=/u01/11gr2/db_1;export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

6) cd <dir-where-cx-oracle-untarred>/cx_Oracle-5.0.4

7) python setup.py install

Once it is installed you can run the sample program from http://wiki.oracle.com/page/Python to make sure it works.

11.2.0.2 Grid infrastructure, private interconnect bonding new feature HAIP

~ rajeevramdas@aol.com ~ Leave a comment

I have been heads down on an Exadata Poc , and only now, got a chance to browse through the new features in 11.2.0.2. The out of place upgrade feature looks interesting.

This new feature in grid infrastructure installation had me really overjoyed (Anyone who has had the pleasure of configuring IPMP, Auto Port Aggregation, Etherchannel etc (based on the o/s) and setting it up correctly to work with Rac, will understand my Joy) . Starting with 11.2.0.2 you do not have to bond the interfaces (If you have redundant GigE nics you are going to use for your private interconnect) you are going to use as the private interconnect. If you have two different interface names to be used for the private interconnect you can provide both the interface names to the oracle grid infrastructure installer and oracle clusterware will create a Highly Available IP Address (HAIP).

Oracle Clusterware, Rac and ASM uses these  load balanced highly available interfaces for communication.

Details can be read at http://download.oracle.com/docs/cd/E11882_01/install.112/e17212/prelinux.htm#BABJHGBE for Linux.

HAIP info can also be found in the 11R2 Clusterware white paper.

Julian Dyke has a blog post that says that MULTICAST has to be enabled for the Network interfaces to enable this to work.

Enabling multicast on the interconnect network is a requirement with 11.2.0.2 Rac.My Oracle Support Notes 1228471.1,1212703.1 details how Multicast can be enabled and checked.

Date and Time Arithmetic in the bash shell

~ rajeevramdas@aol.com ~ Leave a comment

Here are some ways to add hours, days, months etc to a given date and get the result from the bash shell

To add seconds to a given date

rramads@rramads-us2:~$ date -d “2010-08-12 05:30:30 PM 15 seconds”
Thu Aug 12 17:30:45 EDT 2010

To add minutes to a given date in the bash shell you can

rramads@rramads-us2:~$ date -d “2010-08-12 05:30:30 PM 15 minutes”

Thu Aug 12 17:45:30 EDT 2010

To add  hours to a given date in the bash shell you can

rramads@rramads-us2:~$ date -d “2010-08-12 05:30:30 PM 15 hours”

Fri Aug 13 08:30:30 EDT 2010

To add days to a given date

rramads@rramads-us2:~$ date -d “2010-08-12 05:30:30 PM 15 days”

Fri Aug 27 17:30:30 EDT 2010

To add months to a given date

rramads@rramads-us2:~$ date -d “2010-08-12 05:30:30 PM 15 months”
Sat Nov 12 17:30:30 EST 2011

Transportable Tablespace from rman backup

~ rajeevramdas@aol.com ~ Leave a comment

Most of oracle’s MAA documentation for transportable tablespaces, seems to recommend that one should put the tablespaces one is transporting, in read-only mode in the source database, before copying the datafiles to the target. This in most cases means application downtime.

In order to minimize the downtime the recommendations seem to be

  • Create a dataguard physical standby database and use this standby database as the source for transport
  • Create a duplicate (aka clone) of your source and use this new duplicate as the source for transport

If you are only transporting a subset of your tablespaces and you want to minimize your downtime, a 3rd good option is to create and use transportable tablespace sets from your already existing rman backups. This process is documented in the Backup and Recovery users guide, Chapter 26 (11gR2 manual).

Since this process uses an existing rman backup you incur no downtime (ie no need to place tablespaces in read-only mode) on your production systems.

Below are the steps to accomplish this (In my example rk01 is the source database and rk02 is the target database).

  • First off, your source database should be running in archivelog mode
  • Take a full database backup from your source database

export ORACLE_SID=rk01
rman target /

run {
allocate channel oem_backup_disk1 type disk format ‘/u01/orarch/rk01/backup/%U’;
backup as BACKUPSET tag ‘%TAG’ database;
backup as BACKUPSET tag ‘%TAG’ archivelog all not backed up;
release channel oem_backup_disk1;
}
run {
allocate channel oem_backup_disk1 type disk format ‘/u01/orarch/rk01/backup/%U’ maxpiecesize 1000 G;
backup as BACKUPSET tag ‘%TAG’ current controlfile;
release channel oem_backup_disk1;
}

exit;

  • Create a transportable tablespace set for the tablespaces you need to transport

export ORACLE_SID=rk01

rman target /

RMAN>  transport tablespace example

2> tablespace destination ‘/u01/orarch/rk01/datafile’

3> auxiliary destination ‘/u01/orarch/rk01/tmp’;

Once the process is complete rman leaves a copy of the datafile (An operating system file, not a backup set file) for the tablespace example , in the directory /u01/orarch/rk01/datafile. It also leaves a export dump file that has the metadata needed for the transport in the same directory /u01/orarch/rk01/datafile.

  • Do endianness conversions on the files, if you need to go cross platform (Use rman convert)
  • Attach the tablespace to your target database

export ORACLE_SID=rk02

sqlplus / as sysdba

create directory tts_dir as ‘/u01/orarch/rk01/datafile’

/

grant all on directory tts_dir to public

/

Exit;

Before you run the next import, make sure that you have created the schema’s (with appropriate privileges) that are in the tablespace you are transporting in the target database rk02, also make sure any roles that are required are created in the target. Eg: The schema HR has objects in the example tablespace. use the create user command to create the HR user with appropriate privileges in the database rk02.

impdp system/manager dumpfile=dmpfile.dmp directory=tts_dir transport_datafiles=/u01/oradata/rk02/example01.dbf logfile=tts_import.log

So As you can see the whole process is executed without shutting down the source database rk01.

Howto Add a new virtual disk to a virtual box, virtual machine

~ rajeevramdas@aol.com ~ Leave a comment

Once i built a oracle enterprise linux virtual machine, using oracle virtual box (opensource destop virtualization software), i wanted to increase capacity by adding a new virtual disk to the configuration. My original configuration was built using only one virtual disk and this was mounted as the / file system. Now i want to add a new file system /u01 on a new virtual disk. Below are the steps i performed to accomplish this.

First create a new virtual disk using virtual box
Applications -> System Tools -> Oracle VM Virtual Box
File -> Virtual Media Manager
You will see the window below

Click on New to create a New disk. The New Disk creation wizard starts up.

Click on Next

Choose Dynamically expanding storage, Click Next

Choose the location and file name for the virtual box, vdi file. I chose the same directory, where my first vdi file was located (/u02/vimage/oel55). Also choose the size of the new disk you need. Click Next.

The Summary Screen displays summary information regarding the new disk you are configuring. click next

Now add the new virtual disk to your operating system image

Applications -> System Tools -> Oracle VM Virtual Box

Click on the image that you want to add the virtual disk to.

On the right hand side click on Storage

Choose the SATA controller and click on the + button to add a new disk

You can edit the new disk it creates by click on the disk Name and editing the disks properties on the right most window. (Use the properties to pick the new disk you created in the previous step, and give it a new name).

Now once you boot up your image you will see a new disk at your operating system level. In my case the original disk i had was /dev/sda. The new disk i added showed up as /dev/sdb. I partitioned the disk, formatted it, mounted it and added an entry to /etc/fstab so that every time i startup the image this file system will be mounted.

fdisk /dev/sdb

mkfs -t ext3 /dev/sdb1

mkdir /u01

Add the following entry into /etc/fstab

/dev/sdb1 /u01 ext3 defaults 0 2

Then run the command “mount” from the prompt and /u01 will be mounted.

Linux cpu count, cores & threads

~ rajeevramdas@aol.com ~ Leave a comment

To identify the number of physical cpu’s you have on a server you can execute the command

-bash-3.2$ grep ‘physical id’ /proc/cpuinfo | sort -u
physical id : 0
physical id : 1

This means you have 2 cpu’s (You can add a | wc -l to the previous command to just get the count)

To identify the number of cores per cpu you can execute the command

-bash-3.2$ grep ‘cores’ /proc/cpuinfo | sort -u

cpu cores : 4

To check if hyperthreading is turned on in the bios

-bash-3.2$ egrep ‘cores|siblings’  /proc/cpuinfo | sort -u
siblings : 8
cpu cores : 4

If cpu cores equal the number of siblings then hyperthreading is off. If siblings is a multiple of cores then hyperthreading is turned on.

If you want to see the number of cpu’s being seen by the operating systems then you can

grep ‘processor’ /proc/cpuinfo | wc -l

or

run the top command and once the output display’s you can just press the 1 key and it will show the utilization for each cpu on the top.

HowTo create a resource plan using database resource manager

~ rajeevramdas@aol.com ~ Leave a comment

On any database server, there is often only a limited amount of resources (read cpu, memory, i/o). Oracle database servers are often subjected to highly concurrent, resource consuming, queries issued by database client programs. This often leads to contention for cpu, memory and i/o resources on a server. Once you have way more processes on a system actively executing, compared to the number of cpu cores you have, at some point the scheduling of these processes becomes inefficient at the operating system level.

The oracle Database resource manager can be used to throttle the Number of oracle database sessions actively running on a database, or to limit the amount of cpu resources a session gets, or to limit the degree of parallelism each session gets among other things.

Below are the steps to setup a resource plan that

Limits, maximum Degree of Parallelism each session can have, at 64.

Limits the maximum number of Concurrent active sessions that can be present for a user to 2. (The other sessions get queued).

Create a Consumer Group

Login to Enterprise Manager, click on the “Server” tab.

Under the “Resource Manager” section, click on “Consumer Groups”

Click on the “Create” Button

Enter a Name and Description for the Consumer group

Click on the “Add” button to add a database user to the consumer group

Check the box to the right of the user you want to add and click on “Select”

Now click on the “Database Instance” bread crumb to return to the “Server” tab

Click on “Consumer Group Mappings”

Click the radio button on the row that has the value “Oracle User” in the “View” column.

Click on “Add Rule for Selected Type”

In the drop down list “Selected consumer group”, choose “DOP_GROUP”.

In the “Available Oracle User” list, click on the user you want to add to this “DOP_GROUP” group and click on “Move”

Click Ok.

Click on Apply

Click on the “Database Instance” Breadcrump to return to the “Server Tab”

Click on “Resource Plans”

Click on “Create” to create a new resource plan

From the table “Available Group/Subplans” choose “DOP_GROUP” and click on Move. Click Ok.

Click on the tab named “Parallelism”

For the DOP_GROUP set the maximum degree of parallelism to be 64

Click on the tab named “Session Pool”

Set the number of active sessions to “2” (Or any number you want (In the screen i have used 12))

Click “Ok”

In the “Resource Plans” screen, choose the DOP_PLAN, In the actions drop down list, choose “Activate” and click on Go.

Now the resource plan “DOP_PLAN” is active.

Let us say now we submit a bunch of queries (Let us say 20) simultaneously (That use parallel query)as the database user HR then

Only 2 of them will be allowed to run at the same time (The rest are queued)

Each session gets a maximum Degree of parallelism of 64