Enter Your Email

Enter your email address:

Delivered by FeedBurner

Custom Search

Tuesday, June 26, 2007

OCR - Backups: Oracle Real Application Clusters

1. Recent Copies of OCR Backups?
acmrac2-> ocrconfig -showbackup
rac1 2007/06/26 06:33:12 /u01/app/oracle/product/10.2.0/crs_1/cdata/crs
rac1 2007/06/26 02:33:12 /u01/app/oracle/product/10.2.0/crs_1/cdata/crs
rac1 2007/06/25 22:33:11 /u01/app/oracle/product/10.2.0/crs_1/cdata/crs
rac1 2007/06/25 15:09:34 /u01/app/oracle/product/10.2.0/crs_1/cdata/crs
rac1 2007/06/17 17:56:06 /u01/app/oracle/product/10.2.0/crs_1/cdata/crs

2. You can use OCRDUMP to review the contents of the backup.
$ ocrdump -backupfile file_name # file_name is the name of the backup file.
3. What is the frequency of OCR backups automatically done by Oracle Clusteware?
Oracle Clusterware automatically creates OCR backups every 4 hours, each full day and at the end of each week.
More Info on OCR administration:
Refer Administering Oracle Clusterware components.

Sunday, June 17, 2007

Oracle 10g RMAN Cold Backup

oracledb1-> rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Thu May 15 12:47:34 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DGPRD (DBID=223112954)
RMAN> run {
2> #Shutdown Target Database DGPRD For Cold Backup
3> shutdown immediate
4> #Startup Mount, Once the Cold Backup Finishes
5> startup mount
6> #Allocate Channel Now
7> allocate channel c1 TYPE DISK;
8> BACKUP DATABASE FORMAT '/oradata/rman/%d_COLD_%u';
9> #One Backup Completes - Open the database
10> sql 'alter database open';
11> backup current controlfile;
12> sql 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE';
13> release channel c1;
14> }
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 88081256 bytes
Database Buffers 75497472 bytes
Redo Buffers 2932736 bytes

allocated channel: c1
channel c1: sid=157 devtype=DISK

Starting backup at 15-MAY-07
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata/dgprd/system01.dbf
input datafile fno=00003 name=/oradata/dgprd/sysaux01.dbf
input datafile fno=00005 name=/oradata/dgprd/example01.dbf
input datafile fno=00002 name=/oradata/dgprd/undotbs01.dbf
input datafile fno=00004 name=/oradata/dgprd/users01.dbf
channel c1: starting piece 1 at 15-MAY-07
channel c1: finished piece 1 at 15-MAY-07
piece handle=/oradata/rman/DGPRD_COLD_02jgef91 tag=TAG20080515T125704 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:45
Finished backup at 15-MAY-07

Starting Control File and SPFILE Autobackup at 15-MAY-07
piece handle=/oradata/dgprd/flash_recovery_area/DGPRD_PR/autobackup/2008_05_15/o1_mf_s_654785806_42rtyfgg_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-MAY-07

sql statement: alter database open
Starting backup at 15-MAY-07
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current control file in backupset
channel c1: starting piece 1 at 15-MAY-07
channel c1: finished piece 1 at 15-MAY-07
piece handle=/oradata/dgprd/flash_recovery_area/DGPRD_PR/backupset/2008_05_15/o1_mf_ncnnf_TAG20080515T125924_42rtzh7l_.bkp tag=TAG20080515T125924 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 15-MAY-07
\
Starting Control File and SPFILE Autobackup at 15-MAY-07
piece handle=/oradata/dgprd/flash_recovery_area/DGPRD_PR/autobackup/2008_05_15/o1_mf_s_654785968_42rtzltr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-MAY-07

sql statement: ALTER DATABASE BACKUP CONTROLFILE TO TRACE
released channel: c1

RMAN>

Tuesday, June 12, 2007

RMAN: Database Clone, Refresh for Development

UAT Refresh with RMAN
Production site
oracledb1-> hostname
oracledb1.mycorpdomain.com
UAT
oracledb2-> hostname
oracledb2.mycorpdomain.com
1. Create the File system for Backup Dump on the UAT server
oracledb2-> cd dgprd
oracledb2-> mkdir adump bdump cdump udump pfile dpdump
oracledb2-> pwd
/u01/app/oracle/admin/dgprd
oracledb2-> ls -ltr
total 48
drwxr-xr-x 2 oracle oinstall 4096 Jun 2 10:59 udump
drwxr-xr-x 2 oracle oinstall 4096 Jun 2 10:59 pfile
drwxr-xr-x 2 oracle oinstall 4096 Jun 2 10:59 dpdump
drwxr-xr-x 2 oracle oinstall 4096 Jun 2 10:59 cdump
drwxr-xr-x 2 oracle oinstall 4096 Jun 2 10:59 bdump
drwxr-xr-x 2 oracle oinstall 4096 Jun 2 10:59 adump
2. Create the File system for the Oradata, RMAN, FLASH_RECOVERY_AREA on the UAT
oracledb2-> ls -ltr
total 40
drwx------ 2 root root 16384 Mar 28 13:57 lost+found
drwxr-xr-x 2 oracle oinstall 4096 Jun 2 10:52 rman
drwxr-xr-x 2 oracle oinstall 4096 Jun 2 10:52 dgprd
drwxr-xr-x 2 oracle oinstall 4096 Jun 2 10:53 flash_recovery_area
3. List Incarnation - Test
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DGPRD 224754742 CURRENT 1 02-JUN-07
4. Database Uptime, on Production - 2 Ways
SQL> !ps -efgrep pmon
oracle 3877 1 0 10:34 ? 00:00:00 ora_pmon_dgtest
oracle 5092 1 0 11:03 ? 00:00:00 ora_pmon_dgprd
SQL> select to_char(startup_time, 'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
2 FROM sys.v_$instance;
DB Startup Time
--------------------
02-JUN-2007 11:03:14
5. Create a pfile from spfile on the Production database(It will be done at the backup time)
SQL> create pfile='/oradata/rman/initdgprd.ora' from spfile;
File created.
SQL> !ls -ltr /oradata/rman/
total 6920
-rw-r----- 1 oracle oinstall 7061504 Jun 2 11:07 snapcontrolfile_dgprd.f
-rw-r--r-- 1 oracle oinstall 1025 Jun 2 11:23 initdgprd.ora
6. Configure the device type and the FTP location
RMAN> configure channel device type disk format '/oradata/rman/%U';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oradata/rman/%U';
new RMAN configuration parameters are successfully stored

******************** BACKUP DATABASE on PRODUCTION NOW *******************
7. RMAN> backup database plus archivelog;
Starting backup at 02-JUN-07
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 02-JUN-07
Starting backup at 02-JUN-07
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 02-JUN-07
Starting backup at 02-JUN-07
Finished backup at 02-JUN-07
Starting Control File and SPFILE Autobackup at 02-JUN-07
piece handle=/oradata/flash_recovery_area/DGPRD/autobackup/2007_06_02/o1_mf_s_656336693_4485cp8x_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 02-JUN-07
RMAN>
***************************************************************************
8. NOW FTP THE FILEs !!!! Nothing but as Restoring from the TAPE backup.
*********************************************************************************************
oracledb1-> sftp 192.168.2.135
Connecting to 192.168.2.135...
The authenticity of host '192.168.2.135 (192.168.2.135)' can't be established.
RSA key fingerprint is 32:ff:dd:a1:36:1c:0c:71:8a:ef:ad:09:73:ec:e4:1c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.135' (RSA) to the list of known hosts.
oracle@192.168.2.135's password:
sftp> pwd
Remote working directory: /export/home/oracle
sftp> cd /oradata
sftp> cd rman
snapcontrolfile_dgprd.f 100% 6896KB 6.7MB/s 00:01
sftp> mput *
Uploading 01jhtpnp_1_1 to /oradata/rman/01jhtpnp_1_1
01jhtpnp_1_1 100% 8811KB 8.6MB/s 00:00
Uploading 02jhtpns_1_1 to /oradata/rman/02jhtpns_1_1
02jhtpns_1_1 100% 553MB 8.1MB/s 01:07
Uploading 03jhtppj_1_1 to /oradata/rman/03jhtppj_1_1
03jhtppj_1_1 100% 2560 2.5KB/s 00:00
Uploading initdgprd.ora to /oradata/rman/initdgprd.ora
initdgprd.ora 100% 1025 1.0KB/s 00:00
Uploading snapcontrolfile_dgprd.f to /oradata/rman/snapcontrolfile_dgprd.f
snapcontrolfile_dgprd.f 100% 6896KB 6.7MB/s 00:01
***********************************************************************************************************
9. Login to the UAT server
1. Copy the Parameter file to the dbs location
/oradata/rman
oracledb2-> cp -p initdgprd.ora /u01/app/oracle/product/10.2.0/db_1/dbs/
oracledb2-> pwd
/oradata/rman
2. Perform Controlfile Restore *************************************************
oracledb2-> rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 2 11:57:50 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initdgprd.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area 159383552 bytes
Fixed Size 1260648 bytes
Variable Size 58721176 bytes
Database Buffers 96468992 bytes
Redo Buffers 2932736 bytes
RMAN> shutdown abort;
Oracle instance shut down
******************** COPY the parameter file ****************
oracledb2-> cp -p initdgprd.ora /u01/app/oracle/product/10.2.0/db_1/dbs/
oracledb2-> pwd
***************************************************************
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 67109736 bytes
Database Buffers 96468992 bytes
Redo Buffers 2932736 bytes
RMAN> restore controlfile from autobackup;
Starting restore at 02-JUN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
recovery area destination: /oradata/flash_recovery_area
database name (or database unique name) used for search: DGPRD
channel ORA_DISK_1: no autobackups found in the recovery area
autobackup search outside recovery area not attempted because DBID was not set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/02/2007 12:02:16
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

RMAN> restore controlfile from '/oradata/rman/snapcontrolfile_dgprd.f';

Starting restore at 02-JUN-07
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output filename=/oradata/dgprd/control01.ctl
output filename=/oradata/dgprd/control02.ctl
output filename=/oradata/dgprd/control03.ctl
Finished restore at 02-JUN-07

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1
9 ****** Recover the Database Until time ***************************************
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore archivelog all;
Starting restore at 02-JUN-07
Starting implicit crosscheck backup at 02-JUN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 02-JUN-07

Starting implicit crosscheck copy at 02-JUN-07
using channel ORA_DISK_1
Finished implicit crosscheck copy at 02-JUN-07

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oradata/flash_recovery_area/DGPRD/autobackup/2007_06_02/o1_mf_n_656339154_4487rn3f_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log restore to default destination
>>>Removed Few

archive log thread=1 sequence=71
channel ORA_DISK_1: reading from backup piece /oradata/rman/05jhtsib_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradata/rman/05jhtsib_1_1 tag=TAG20070602T123211
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=72
channel ORA_DISK_1: reading from backup piece /oradata/rman/07jhtsk5_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradata/rman/07jhtsk5_1_1 tag=TAG20070602T123309
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 02-JUN-07
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DGPRD 224754742 PARENT 1 02-JUN-07
2 2 DGPRD 224754742 CURRENT 225172 02-JUN-07
RMAN> reset database to incarnation 1;
database reset to incarnation 1
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DGPRD 224754742 CURRENT 1 02-JUN-07
2 2 DGPRD 224754742 ORPHAN 225172 02-JUN-07

RMAN> run {
2> set until time "to_date('02-JUNE-2007 12:20:00','DD-MON-YYYY HH24:MI:SS')";
3> restore database;
4> recover database;
5> sql 'alter database open resetlogs';
6> }

executing command: SET until clause

Starting restore at 02-JUN-07
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata/dgprd/system01.dbf
restoring datafile 00002 to /oradata/dgprd/undotbs01.dbf
restoring datafile 00003 to /oradata/dgprd/sysaux01.dbf
restoring datafile 00004 to /oradata/dgprd/users01.dbf
channel ORA_DISK_1: reading from backup piece /oradata/rman/02jhtpns_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradata/rman/02jhtpns_1_1 tag=TAG20070602T114355
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 02-JUN-07

Starting recover at 02-JUN-07
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 58 is already on disk as file /oradata/dgprd/arc/1_58_656333366.arc
archive log thread 1 sequence 59 is already on disk as file /oradata/dgprd/arc/1_59_656333366.arc
archive log filename=/oradata/dgprd/arc/1_58_656333366.arc thread=1 sequence=58
archive log filename=/oradata/dgprd/arc/1_59_656333366.arc thread=1 sequence=59
media recovery complete, elapsed time: 00:00:04
Finished recover at 02-JUN-07
sql statement: alter database open resetlogs
RMAN>

Sunday, June 03, 2007

Automatic Stroage Management (ASM): Adding a disk to a existing Diskgroup

Automatic Storage Management:
Adding a Disk to an existing Disk Group:
Oracle 10G RAC:
In day to day activities DBA needs plan and assign storage to the growing database. It’s a brief idea & procedure about how to add a new disk to an existing disk groups.

* Please refer my other posts on ASM and Linux VMware for adding Disks (Shortly edited)
1. Login as a root user and check for the new disk which was just added at the VMware.
[root@rac1 ~]# fdisk -l
Disk /dev/sda: 32.2 GB, 32212254720 bytes
255 heads, 63 sectors/track, 3916 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
[…Truncated]
Disk /dev/sdf doesn't contain a valid partition table
2. Use fdisk to create the partition info for the newly added disk.
*************************************

[root@rac1 ~]# fdisk /dev/sdf
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.
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-261, default 1): 1
Last cylinder or +size or +sizeM or +sizeK (1-261, default 261):
Using default value 261
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
*************************************

3. You can see the new partition info; you can do this on both the Cluster nodes.
******************************************

[root@rac1 ~]# fdisk -l
Disk /dev/sdf: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdf1 1 261 2096451 83 Linux
[root@rac1 ~]#
****************************************************
NODE 02
[root@rac2 ~]# fdisk -l
Disk /dev/sdf: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdf1 1 261 2096451 83 Linux

***********************************************
4. In Linux you can use ASM library driver provided by Oracle to configure the new disks.
************************************
oracleasm-support-2.0.3-2
oracleasm-2.6.9-42.0.0.0.1.EL-2.0.3-2
oracleasmlib-2.0.2-1
oracleasm-2.6.9-42.0.0.0.1.ELsmp-2.0.3-2
NODE 01 # Check for existing ASM disks
[root@rac1 ~]# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks: [ OK ]
[root@rac1 ~]# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
NODE 02 # Check for existing ASM disks
[root@rac2 ~]# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks: [ OK ]
[root@rac2 ~]# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
*********************************

5. Create ASM disks. Do this on any one node as a root user.
[root@rac1 ~]# /etc/init.d/oracleasm createdisk VOL4 /dev/sdf1
Marking disk "/dev/sdf1" as an ASM disk: [ OK ]
[root@rac1 ~]# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks: [ OK ]
[root@rac1 ~]# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
VOL4

Check the ASM Disks from NODE 2
[root@rac2 ~]# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks: [ OK ]
[root@rac2 ~]# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
VOL4
********************************

6. Configure X-Windows and run DBCA as a oracle user from the primary node, to add the disk to a existing disk group.
7. DBCA: Welcome screen: Select Oracle Real Application clusters database
8. DBCA: Operations Screen: Select Configure Automatic Storage Management ->NEXT
9. Node Selection: Select both the nodes -> Next
10. ASM Disk Groups: Select the Disk Group Name, to assing a new disk
11. Add Disks Screen: Select VOL4, we added before with ASM library
This ends up adding space to a existing Disk group.
** This is for knowledge purpose of knowing how and was experimented on free version software and Operating systems.
Please refer other posts on Linux VMware & Oracle ASM.
 
Copyright © 2005 - 2008 DBA-ONWeb Technical blog. All rights reserved