Backu and Recovery

In this document, I discussed how to take backup (source) and recover (target/other m/c) the database in different machine in NOARCHIVE MODE.

Following are two important parameters which need to set accordingly.

Alter System Set

db_recovery_file_dest_size=20G scope=both

/

Alter System Set

db_recovery_file_dest= ''F:\oracle\product\10.2.0\flash_recovery_area'' scope=both

/

Taking Backup (RMAN)

1. Shut down Database

Sql> Shutdown immediate;

2. Startup database in MOUNT mode.

Sql> Startup Mount;

3. Now start RMAN (by typing RMAN at command prompt)

C:> RMAN TARGET /

4. Check the configuration

RMAN> SHOW ALL;

5. Check for default device and CONTROLFILE AUTOBACKUP parameters.

Default Device should be DISK (if backup is on harddisk)

CONTROLFILE AUTOBACKUP should be ON; (This command will take the control files and spfile auto backup).

Execute below statements

RMAN>CONFIGURE DEFAULT DEVICE TYPE TO DISK;

RMAN>CONFIGURE CONTROLFILE AUTOBACKUP On;

6. Take the backup

RMAN> BACKUP DATABASE;

Recovery Process

Backup Files should copied to target machine ( db_recovery_file_dest ) directory. Usually this will be \flash_recovery_area directory in oracle home.

Restore the database to different location/machine with different file structures

1.shutdown immediate

sql>shutdown immediate;

2.Startup nomount

sql> startup nomount

3. Connect to RMAN

C:> RMAN TARGET /

4. Restore the control file

RMAN> restore controlfile from autobackup;

if backup is done in different location we have to mention the location

restore controlfile from autobackup recovery area='E:\oracle\product\10.2.0/flash_recovery_area' db_name='wg';

5.Alter the database (to get mount)

sql> Alter Database Mount;

6. Change the path names (as files stored in source m/c is different from target m/c (directories)

RMAN> run {

set newname for datafile 1 to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\SYSTEM01.DBF';

set newname for datafile 2 to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\UNDOTBS01.DBF';

set newname for datafile 3 to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\SYSAUX01.DBF';

set newname for datafile 4 to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\USERS01.DBF';

set newname for datafile 5 to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\EXAMPLE01.DBF';

}

7. Alter the database and rename with new file names

SQL>alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\REDO03.LOG' to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\REDO03.LOG';

SQL>alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\REDO02.LOG' to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\REDO02.LOG';

SQL>alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\REDO01.LOG' to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\REDO01.LOG';

SQL>alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\SYSTEM01.DBF' to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\SYSTEM01.DBF';

SQL>alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\UNDOTBS01.DBF' to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\UNDOTBS01.DBF';

SQL>alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\SYSAUX01.DBF' to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\SYSAUX01.DBF';

SQL>alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\USERS01.DBF' to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\USERS01.DBF';

SQL>alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\EXAMPLE01.DBF' to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\EXAMPLE01.DBF';

8. Restore the database using restore command.

RMAN> restore database;

9. Open the database with resetlogs option

Sql> Alter Database Open resetlogs;

10. With above command recovery process is completed. Users can now logon to new machine with same password as source.