Archive for the ‘Oracle Backup Recovery’ Category

If one of your data file is corrupted in Production or in Standby, you can pick that data file from other location and restore.
We don’t have to recreate the standby db or restore the DB. Please find the steps we followed..

SQL> select * from v$database_block_corruption;

FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
36     289469          1                  0 CORRUPT
75      96029          1                  0 CORRUPT

Primary:

RMAN> backup as copy datafile 36 format ‘/ora_dr_backup/rman/drdb/data36.dbf’;

RMAN>backup as copy datafile 75 format ‘/ora_dr_backup/rman/drdb/data75.dbf’;

ftp Standby

SQL> alter database datafile 36 offline ;   If your standby datafile is corrupted use ‘drop’ option
SQL> alter database datafile 75 offline ;  If your standby datafile is corrupted use ‘drop’ option

RMAN> catalog datafilecopy  ‘/ora_dr_backup/rman/drdb/data36.dbf’;

RMAN> catalog datafilecopy ‘/ora_dr_backup/rman/drdb/data75.dbf’;

RMAN> list copy of datafile 36; ==> Check above mountpoint is listed

RMAN> list copy of datafile 75; ==> Check above mountpoint is listed

RMAN> restore datafile 36;

SQL> alter database datafile 36 online;

RMAN> recover datafile 36;

RMAN> restore datafile 75;

SQL> alter database datafile 75 online;

RMAN> recover datafile 75;

convert this post to pdf.

Posted by Borndba on February 8, 2011 at 5:13 pm under Oracle Backup Recovery.
Comments Off.

Recently while applying incremental backup in DG running under ASM, got into issue due the rman backup taken with %U.

May be should have used %N%f.dbf this helps in giving both tablespace name and absolute file_id. This information is documented in

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta033.htm#sthref531

convert this post to pdf.

Posted by Borndba on May 27, 2010 at 8:58 am under Oracle Backup Recovery, Uncategorized.
Comments Off.

It’s common missing the arch log file needed for standby recovery, there by standby going out of sync. One way before 10g is rebuilding the standby from scratch.

From 10g, you can create a incremental backup from primary and apply the incremental to standby site.

For Source and Target in ASM, use metalink note

Steps to perform for Rolling forward a standby database using RMAN incremental backup when primary and standby are in ASM filesystem [ID 836986.1]

BACKUP FROM SOURCE:

~~~~~~~~~~~~~~~~~~~

get the SCN from checkpoint_change#@v$datafile_header  of standby database. You can also take the current_scn@v$database. But v$database value comes from controlfile and if the controlfile value is recent than datafile, you may not be successful.

RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 329412309 DATABASE FORMAT ‘/local/eastus/orabackup00/rman/incr1_%U’;

if you want compressed backup

BACKUP AS COMPRESSED backupset DEVICE TYPE DISK INCREMENTAL FROM SCN 8977054477894  DATABASE FORMAT  ‘/local/eastus/orabackup00/rman/incr1_%U’;

Starting backup at 06-MAY-09

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=422 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00015 name=/local/eastus/oradata01/eastusp1/eastusp1rdgdbo_data01.dbf

input datafile fno=00002 name=/local/eastus/oradata03/eastusp1/eastusp1undotbs01.dbf

input datafile fno=00010 name=/local/eastus/oradata05/eastusp1/eastusp1audit_data01.dbf

input datafile fno=00014 name=/local/eastus/oradata05/eastusp1/eastusp1_sysaux_01.dbf

input datafile fno=00013 name=/local/eastus/oradata05/eastusp1/eastusp1statspack_data.dbf

input datafile fno=00008 name=/local/eastus/oradata05/eastusp1/eastusp1xdb01.dbf

input datafile fno=00004 name=/local/eastus/oradata05/eastusp1/eastusp1drsys01.dbf

input datafile fno=00009 name=/local/eastus/oradata05/eastusp1/netiq_data01.dbf

input datafile fno=00003 name=/local/eastus/oradata05/eastusp1/eastusp1cwmlite01.dbf

input datafile fno=00005 name=/local/eastus/oradata05/eastusp1/eastusp1odm01.dbf

input datafile fno=00001 name=/local/eastus/oradata00/eastusp1/eastusp1system01.dbf

input datafile fno=00006 name=/local/eastus/oradata00/eastusp1/eastusp1tools01.dbf

input datafile fno=00007 name=/local/eastus/oradata00/eastusp1/eastusp1users01.dbf

input datafile fno=00012 name=/local/eastus/oradata02/eastusp1/eastusp1east4_idx01.dbf

input datafile fno=00017 name=/local/eastus/oradata02/eastusp1/eastusp1rdgdbo_idx01.dbf

input datafile fno=00011 name=/local/eastus/oradata01/eastusp1/eastusp1east4_data01.dbf

input datafile fno=00016 name=/local/eastus/oradata01/eastusp1/eastusp1ASI_Data01.dbf

channel ORA_DISK_1: starting piece 1 at 06-MAY-09

channel ORA_DISK_1: finished piece 1 at 06-MAY-09

piece handle=/local/eastus/orabackup00/rman/incr1_tvkebumh_1_1 tag=TAG20090506T160952 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:07:05

Finished backup at 06-MAY-09

TRANSFER THE INCREMENTAL BACKUP TO TARGET

Catalog the incremental backup. This help rman to indentify the new backup.

RMAN> CATALOG START WITH ‘/local/eastus/orabackup00/rman/incr1_tvkebumh_1_1′;

searching for all files that match the pattern /local/eastus/orabackup00/rman/incr1_tvkebumh_1_1

List of Files Unknown to the Database

=====================================

File Name: /local/eastus/orabackup00/rman/incr1_tvkebumh_1_1

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files…

cataloging done

List of Cataloged Files

=======================

File Name: /local/eastus/orabackup00/rman/incr1_tvkebumh_1_1

Recover with redo clause

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 06-MAY-09

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: /local/eastus/oradata00/eastusp1/eastusp1system01.dbf

destination for restore of datafile 00002: /local/eastus/oradata03/eastusp1/eastusp1undotbs01.dbf

destination for restore of datafile 00003: /local/eastus/oradata05/eastusp1/eastusp1cwmlite01.dbf

destination for restore of datafile 00004: /local/eastus/oradata05/eastusp1/eastusp1drsys01.dbf

destination for restore of datafile 00005: /local/eastus/oradata05/eastusp1/eastusp1odm01.dbf

destination for restore of datafile 00006: /local/eastus/oradata00/eastusp1/eastusp1tools01.dbf

destination for restore of datafile 00007: /local/eastus/oradata00/eastusp1/eastusp1users01.dbf

destination for restore of datafile 00008: /local/eastus/oradata05/eastusp1/eastusp1xdb01.dbf

destination for restore of datafile 00009: /local/eastus/oradata05/eastusp1/netiq_data01.dbf

destination for restore of datafile 00010: /local/eastus/oradata05/eastusp1/eastusp1audit_data01.dbf

destination for restore of datafile 00011: /local/eastus/oradata01/eastusp1/eastusp1east4_data01.dbf

destination for restore of datafile 00012: /local/eastus/oradata02/eastusp1/eastusp1east4_idx01.dbf

destination for restore of datafile 00013: /local/eastus/oradata05/eastusp1/eastusp1statspack_data.dbf

destination for restore of datafile 00014: /local/eastus/oradata05/eastusp1/eastusp1_sysaux_01.dbf

destination for restore of datafile 00015: /local/eastus/oradata01/eastusp1/eastusp1rdgdbo_data01.dbf

destination for restore of datafile 00016: /local/eastus/oradata01/eastusp1/eastusp1ASI_Data01.dbf

destination for restore of datafile 00017: /local/eastus/oradata02/eastusp1/eastusp1rdgdbo_idx01.dbf

channel ORA_DISK_1: reading from backup piece /local/eastus/orabackup00/rman/incr1_tvkebumh_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/local/eastus/orabackup00/rman/incr1_tvkebumh_1_1 tag=TAG20090506T160952

channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

Finished recover at 06-MAY-09

RMAN>

You can check the checkpoint_change#@datafile_header, it should have increased and should be equal to the point you started the incremental backup.

Controlfile of standby may not be updated after this excercise. Recreate the standby controlfile from primary again.

convert this post to pdf.

Posted by Borndba on May 7, 2009 at 2:06 am under Oracle Backup Recovery, Oracle Dataguard/RAC.
Comments Off.

Recently we had a scenario, where we need to migrate a oracle 7 db to oracle 9i. We have decided to go ahead with exp/imp method. Size of the Oracle 7 is 8-10gb. Space available in this box is 2.5gb. So we can’t take a complete export, as the size of the dump file may go beyond. Even the compress export using mknod is crossing 2.5 gb. Also there is no filesize/volsize option in oracle7 export utility.
We have did something like this.

 cd /tmp/data
 rm exp.dmp
 mknod exp.dmp p       # mkfifo on certain Unix flavours
 split -b1024m < /tmp/data/exp.dmp &
 exp scott/tiger file=/tmp/data/exp.dmp

Export will create a file of 1024m each, which we have to transfer to oracle9i box and do the import like,

 cd /tmp/data
 rm exp.dmp
 mknod exp.dmp p
 cat xaa xab xac xad > /tmp/data/exp.dmp &
 imp scott/tiger file=/tmp/data/exp.dmp commit=y tables=tableX

From 8, we can use filesize to generate multiple files of specific size. But here we may have to give in advance the number of file names. But you can use the below trick.
“file=xyz%u.dmp”



Visitors Count


convert this post to pdf.

Posted by Borndba on April 4, 2009 at 12:18 am under Oracle, Oracle Backup Recovery.
Tags: ,
Comments Off.

Deletefile Procedure in Dbms_Restore_Backup.

There is an unsupported procedure “Deletefile” in Dbms_Restore_Backup. This procedure can delete the OS files. Make sure you grant this package to right users. This will be one more security violation if used improperly.

c:scripts>dir d:oracleproduct10.2.0adminaryaaudump1.trc
 Volume in drive D has no label.
 Volume Serial Number is 8C01-73AF

 Directory of d:oracleproduct10.2.0adminaryaaudump

10/13/2006  10:29 AM            11,574 1.trc
               1 File(s)         11,574 bytes
               0 Dir(s)   4,653,568,000 bytes free

Then from sqlplus

sys@10.2.0.2.0> Begin
  2  dbms_backup_restore.deletefile('D:oracleproduct10.2.0
adminARYAAudump1.trc');
  3  end;
  4  /

PL/SQL procedure successfully completed.

C:Documents and SettingsAryaa>dir D:oracleproduct10.2.0adminARYAAudump1.trc
 Volume in drive D has no label.
 Volume Serial Number is 8C01-73AF

 Directory of D:oracleproduct10.2.0adminARYAAudump

File Not Found



Visitors Count


convert this post to pdf.

Posted by Borndba on April 4, 2009 at 12:15 am under Oracle, Oracle Backup Recovery.
Tags: ,
Comments Off.

RMAN backups empty block

RMAN backups all the blocks which are ever used, it means backups blocks above HWM of datafiles (Note it is datafile and not HWM of object).

1. I have created a tablespace and created a empty table. Then took a RMAN
tablespace level backup. The size was 96k
2. I have populated this table with 6million data and took RMAN tablespace level
backup. The size is 704mb.
3. I have deleted all the data, the backup size was 704mb.
4. Now i truncated the table to bring the HWM down, even this time the backup
size is 704mb.
5. Finally i droped the table, again this time the backup size is 704mb.

This tablespace has got only one object.

The reason is RMAN considers HWM of datafile and not objects. However this behaviour can be changed in 10gR2 using “Change tracking files”.



Visitors Count

class="statcounter">
href=”http://www.statcounter.com/blogger/”
target=”_blank”> src="http://c.statcounter.com/4628332/0/06d2759c/0/"
alt="blogspot visitor" >


convert this post to pdf.

Posted by Borndba on April 3, 2009 at 11:54 pm under Oracle, Oracle Backup Recovery.
Tags: ,
Comments Off.

BUFFER=Y in export

In direct mode of export, BUFFER parameter is not valid. Instead use RECORDLENGTH. If you don’t specify RECORDLENGTH in direct mode export then i _guess_ it will only take 1k. You can set upto 65535 for RECORDLENGTH.



Visitors Count


convert this post to pdf.

Posted by Borndba on April 3, 2009 at 11:40 pm under Oracle, Oracle Backup Recovery.
Tags: ,
Comments Off.