RMAN basic BACKUP and RECOVERY (no catalog)

Oracle Recovery Manager (RMAN)

RMAN was introduced in Oracle8. Recovery Manager(RMAN) is an Oracle provided (free) utility for backing-up, restoring and recovering Oracle databases. RMAN ships with the Oracle database and doesn’t require a separate installation. The RMAN executable is located in $ORACLE_HOME/bin directory.

Prerequisites:-

1) Target database has to run in Archive log mode for this scenario

2) create a user

3) create a table on user, which we created just

4) Insert values

Testing

1. A) Click on start => type CMD => right click on CMD icon => click on Run as Administrator 

C:Windowssystem32>set ORACLE_SID=PROD

C:Windowssystem32>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 – Production on Fri Jul 13 18:25:25 2012

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, OLAP and Data Mining options

SQL> create user sagar identified by sagar default tablespace users;

User created.

SQL> grant connect, resource to test;

Grant succeeded.

SQL> conn test/test

Connected.

SQL> create table rman_test (n number(1));

Table created.

SQL> insert into rman_test values (1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from rman_test;

COUNT(*)

———-

5

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production

With the Partitioning, OLAP and Data Mining options

1. b) Connect to RMAN client 

C:Windowssystem32>rman target /

Recovery Manager: Release 10.2.0.3.0 – Production on Fri Jul 13 18:28:20 2012

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

connected to target database: PROD (DBID=200218226)

RMAN> backup database;

Starting backup at 13-JUL-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=C:ORACLEPRODUCT10.2.0ORADATAPRODSYSTEM01.DBF

input datafile fno=00003 name=C:ORACLEPRODUCT10.2.0ORADATAPRODSYSAUX01.DBF

input datafile fno=00002 name=C:ORACLEPRODUCT10.2.0ORADATAPRODUNDOTBS01.DBF

input datafile fno=00004 name=C:ORACLEPRODUCT10.2.0ORADATAPRODUSERS01.DBF

channel ORA_DISK_1: starting piece 1 at 13-JUL-12

channel ORA_DISK_1: finished piece 1 at 13-JUL-12

piece handle=C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAPRODBACKUPSET2012_07_13O1_MF_NNNDF_TAG2

0120713T183036_8006ZNYX_.BKP tag=TAG20120713T183036 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 13-JUL-12

channel ORA_DISK_1: finished piece 1 at 13-JUL-12

piece handle=C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAPRODBACKUPSET2012_07_13O1_MF_NCSNF_TAG2

0120713T183036_80071FQN_.BKP tag=TAG20120713T183036 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 13-JUL-12

RMAN> list backup of controlfile ;

List of Backup Sets

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

——- —- — ———- ———– ———— —————

14      Full    6.80M      DISK        00:00:00     12-JUL-12

BP Key: 14   Status: AVAILABLE  Compressed: NO  Tag: TAG20120712T204245

Piece Name: C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAPRODBACKUPSET2012_07_12O1_MF_NCS

NF_TAG20120712T204245_7ZXTDWLT_.BKP

Control File Included: Ckp SCN: 644200       Ckp time: 12-JUL-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

——- —- — ———- ———– ———— —————

16      Incr 1  6.80M      DISK        00:00:02     12-JUL-12

BP Key: 16   Status: AVAILABLE  Compressed: NO  Tag: TAG20120712T211612

Piece Name: C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAPRODBACKUPSET2012_07_12O1_MF_NCS

N1_TAG20120712T211612_7ZXWCXFS_.BKP

Control File Included: Ckp SCN: 649612       Ckp time: 12-JUL-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

——- —- — ———- ———– ———— —————

18      Full    6.80M      DISK        00:00:03     13-JUL-12

BP Key: 18   Status: AVAILABLE  Compressed: NO  Tag: TAG20120713T183036

Piece Name:C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAPRODBACKUPSET2012_07_13O1_MF_NCS

NF_TAG20120713T183036_80071FQN_.BKP

Control File Included: Ckp SCN: 692265       Ckp time: 13-JUL-12

1. C) Make a note of path and name of Controlfile

Now get out of RMAN

RMAN > EXIT

Recovery Manager Complete

1. D) Now shutdown the database. Remove all files under oradata location

SQL*Plus: Release 10.2.0.3.0 – Production on Fri Jul 13 18:35:42 2012

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, OLAP and Data Mining options

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

NOTE: (This is just understanding purpose. You must not do this step at customer site)

E ) Delete all files under this directory C:ORACLEPRODUCT10.2.0oradataPROD

Now go to RMAN client  Connect to target database

C:Windowssystem32>rman target /

Recovery Manager: Release 10.2.0.3.0 – Production on Fri Jul 13 18:45:01 2012

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

connected to target database (not started)

Note :Since database is in down mode, RMAN does not show the Database name and status of Database

RMAN> startup nomount;

Oracle instance started

Total System Global Area    1199570944 bytes

Fixed Size                     1291988 bytes

Variable Size                293603628 bytes

Database Buffers             897581056 bytes

Redo Buffers                   7094272 bytes

RMAN> restore controlfile from ‘C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAPRODBACKUPSET2012_07_

13O1_MF_NCSNF_TAG20120713T183036_80071FQN_.BKP’;

Starting restore at 13-JUL-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: restoring control file

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

output filename=C:ORACLEPRODUCT10.2.0ORADATAPRODCONTROL01.CTL

output filename=C:ORACLEPRODUCT10.2.0ORADATAPRODCONTROL02.CTL

output filename=C:ORACLEPRODUCT10.2.0ORADATAPRODCONTROL03.CTL

Finished restore at 13-JUL-12

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 13-JUL-12

Starting implicit crosscheck backup at 13-JUL-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

Crosschecked 5 objects

Finished implicit crosscheck backup at 13-JUL-12

Starting implicit crosscheck copy at 13-JUL-12

using channel ORA_DISK_1

Finished implicit crosscheck copy at 13-JUL-12

searching for all files in the recovery area

cataloging files…

cataloging done

List of Cataloged Files

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

File Name: C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAPRODBACKUPSET2012_07_13O1_MF_NCSNF_TAG201

20713T183036_80071FQN_.BKP

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 C:ORACLEPRODUCT10.2.0ORADATAPRODSYSTEM01.DBF

restoring datafile 00002 to C:ORACLEPRODUCT10.2.0ORADATAPRODUNDOTBS01.DBF

restoring datafile 00003 to C:ORACLEPRODUCT10.2.0ORADATAPRODSYSAUX01.DBF

restoring datafile 00004 to C:ORACLEPRODUCT10.2.0ORADATAPRODUSERS01.DBF

channel ORA_DISK_1: reading from backup piece C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAPRODBACK

UPSET2012_07_13O1_MF_NNNDF_TAG20120713T183036_8006ZNYX_.BKP

channel ORA_DISK_1: restored backup piece 1

piece handle=C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAPRODBACKUPSET2012_07_13O1_MF_NNNDF_TAG2

0120713T183036_8006ZNYX_.BKP tag=TAG20120713T183036

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

Finished restore at 13-JUL-12

RMAN> recover database;

Starting recover at 13-JUL-12

using channel ORA_DISK_1

starting media recovery

unable to find archive log

archive log thread=1 sequence=2

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 07/13/2012 18:55:30

RMAN-06054: media recovery requesting unknown log: thread 1 seq 2 lowscn 692242

RMAN> alter database open resetlogs;

database opened

RMAN> exit

Recover Manager Complete.

1. F) Since we started our database and opened through RMAN.

Just connect as sagar user

C:Windowssystem32>set ORACLE_SID=PROD

C:Windowssystem32>sqlplus test/test

SQL*Plus: Release 10.2.0.3.0 – Production on Fri Jul 13 19:09:59 2012

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, OLAP and Data Mining options

SQL> select count(*) from rman_test;

COUNT(*)

———-

5

SQL> select * from rman_test;

N

———-

1

1

1

1

1

SQL>

We successfully done with  backups and recovery using RMAN

Share this post: on Twitter on Facebook on Google+