RMAN backup and Recovery (Basics)
=================================
This doc aims to provide a basic solutions for the following targets :
1. Make an incremental 1 database backup using a tag
2. Backup control files and spfile.
3. Checks backups
4. Restore the control file and spfile
5. Restore a database at a point in time
6. Clonning a database from RMAN
7. Backup RMAN catalog
8. Rman commands
First learn how to connect
=================================
C:\>rman target / catalog rman/rman@rman
Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 19 22:49:16 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1150787593)
connected to recovery catalog database
Make an incremental 1 database backup using a tag
===========================================
C:\>set oracle_sid=orcl
C:\rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon May 14 12:28:56 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1144457296)
RMAN> connect catalog rman/rman@rman
connected to recovery catalog database
Register the database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Backup database
resync catalog;
run
{
allocate channel ch1 type disk;
backup incremental level 1 as backupset tag=orcl_L1 (database);
sql 'alter system archive log current';
release channel ch1;
allocate channel ch1 type disk;
backup
archivelog all
delete all input;
release channel ch1;
}
Backup Control file and Spfile
===========================================
You can enable the auto backup feature , that auto backup control files and spfiles . These files are included in the datafile’s backup set.
Rman>configure auto backup on
When you list your backup set you can see
…
Control File Included: Ckp SCN: 147650 Ckp time: 19-MAY-07
SPFILE Included: Modification time: 19-MAY-07
….
Check your backup
===========================================
The following command you can help to monitor your backup
RMAN>backup validate batabase;
The above command check the data file’s location , and its physical and logical corruption status. If some error occurred the view V$database_block_corruption is updated by RMAN.
RMAN>List backup [ tag=your tag name]
RMAN>List backupset;
RMAN>List copy;
RMAN>List backup summary;
RMAN>crosscheck backup;
Example:
RMAN> List backupset tag=orcl_l1;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1410 Incr 1 56.00K DISK 00:00:25 19-MAY-07
BP Key: 1414 Status: AVAILABLE Compressed: NO Tag: ORCL_L1
Piece Name: C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_19\O1_MF_NNND1_ORCL_L1_34Y49O4D_.BKP
List of Datafiles in backup set 1410
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 1 Incr 147640 19-MAY-07 C:\ORADATA\ORCL\SYSTEM01.DBF
2 1 Incr 147640 19-MAY-07 C:\ORADATA\ORCL\UNDOTBS01.DBF
3 1 Incr 147640 19-MAY-07 C:\ORADATA\ORCL\SYSAUX01.DBF
4 1 Incr 147640 19-MAY-07 C:\ORADATA\ORCL\USERS01.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1411 Incr 1 6.80M DISK 00:00:03 19-MAY-07
BP Key: 1415 Status: AVAILABLE Compressed: NO Tag: ORCL_L1
Piece Name: C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_19\O1_MF_NCSN1_ORCL_L1_34Y4BJHN_.BKP
Control File Included: Ckp SCN: 147650 Ckp time: 19-MAY-07
SPFILE Included: Modification time: 19-MAY-07
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1486 Incr 1 6.44M DISK 00:00:27 19-MAY-07
BP Key: 1489 Status: AVAILABLE Compressed: NO Tag: ORCL_L1
Piece Name: C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_19\O1_MF_NNND1_ORCL_L1_34YKTZTC_.BKP
List of Datafiles in backup set 1486
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 1 Incr 150555 19-MAY-07 C:\ORADATA\ORCL\SYSTEM01.DBF
2 1 Incr 150555 19-MAY-07 C:\ORADATA\ORCL\UNDOTBS01.DBF
3 1 Incr 150555 19-MAY-07 C:\ORADATA\ORCL\SYSAUX01.DBF
4 1 Incr 150555 19-MAY-07 C:\ORADATA\ORCL\USERS01.DBF
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1369 B 1 A DISK 19-MAY-07 1 1 NO TAG20070519T175846
1370 B 1 A DISK 19-MAY-07 1 1 NO TAG20070519T175846
1394 B A A DISK 19-MAY-07 1 1 NO TAG20070519T180010
1410 B 1 A DISK 19-MAY-07 1 1 NO ORCL_L1
1411 B 1 A DISK 19-MAY-07 1 1 NO ORCL_L1
1443 B A A DISK 19-MAY-07 1 1 NO TAG20070519T180148
1486 B 1 A DISK 19-MAY-07 1 1 NO ORCL_L1
1499 B F A DISK 19-MAY-07 1 1 NO TAG20070519T215253
1534 B A A DISK 19-MAY-07 1 1 NO TAG20070519T215309
1547 B F A DISK 19-MAY-07 1 1 NO TAG20070519T215314
Restore spfile and control files
===========================================
Restore spfile
C:\>rman target / catalog rman/rman@rman
Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 19 23:12:49 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
connected to recovery catalog database
RMAN> startup
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\MY_DATABASE_1023\DATABASE\INITORCL.ORA'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 05/19/2007 23:13:06
ORA-01103: database name 'ORCL' in control file is not 'DUMMY'
RMAN> set dbid 1150787593
executing command: SET DBID
database name is "ORCL" and DBID is 1150787593
RMAN> restore spfile;
Starting restore at 19-MAY-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring SPFILE
output filename=C:\MY_DATABASE_1023\DATABASE\SPFILEORCL.ORA
channel ORA_DISK_1: reading from backup piece C:\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2007_05_19\O1_MF_S_623026
4YOKL8M_.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2007_05_19\O1_MF_S_623026528_34YOKL8M_.BKP tag=TAG20070519
28
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 19-MAY-07
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area 167772160 bytes
Fixed Size 1246852 bytes
Variable Size 62916988 bytes
Database Buffers 100663296 bytes
Redo Buffers 2945024 bytes
Restore control files
RMAN> startup
Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 05/19/2007 23:19:18
ORA-00205: error in identifying control file, check alert log for more info
RMAN> set dbid 1150787593;
executing command: SET DBID
database name is "ORCL" and DBID is 1150787593
RMAN> restore controlfile;
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database;
RMAN> recover database;
Starting recover at 19-MAY-07
Starting implicit crosscheck backup at 19-MAY-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 13 objects
Finished implicit crosscheck backup at 19-MAY-07
Starting implicit crosscheck copy at 19-MAY-07
using channel ORA_DISK_1
Finished implicit crosscheck copy at 19-MAY-07
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: C:\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2007_05_19\O1_MF_1_90_34YOKWQ8_.ARC
File Name: C:\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2007_05_19\O1_MF_S_623026528_34YOKL8M_.BKP
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 90 is already on disk as file C:\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2007_05
_1_90_34YOKWQ8_.ARC
archive log thread 1 sequence 91 is already on disk as file C:\ORADATA\ORCL\REDO01.LOG
archive log filename=C:\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2007_05_19\O1_MF_1_90_34YOKWQ8_.ARC thread=1 s
0
archive log filename=C:\ORADATA\ORCL\REDO01.LOG thread=1 sequence=91
media recovery complete, elapsed time: 00:00:02
Finished recover at 19-MAY-07
RMAN> sql 'alter database open resetlogs';
sql statement: alter database open resetlogs
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1281 1282 ORCL 1150787593 PARENT 1 19-MAY-07
1281 2410 ORCL 1150787593 CURRENT 153553 19-MAY-07
Cloning database with RMAN
===========================================
Terms
Source database: The original database
Auxiliary database: The new database ( after cloning)
Steps
Create the password and init files
Set you listener to manual register both source and auxiliary database
Create a tns entry in tnsnames.ora for the auxiliary database
Test the tns entry
Backup ( LEVEL 0 ) the source database
Open nomount the auxiliary database and EXIT the session
Connect to RMAN using the auxiliary database
Clone the database
Step1
=====
C:\>oradim -NEW -SID ORCL2 -STARTMODE AUTO -SHUTMODE IMMEDIATE
Instance created.
C:\>orapwd file=C:\My_Database_1023\database\pwdorcl2.ora password=oracle entries=5
Step 2
======
C:\>tnsping orcl2
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 20-MAY-2007 00:30:50
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
C:\My_Database_1023\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = michaelgeorgiou.domain.local)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl2)))
OK (130 msec)
Step 5
=======
resync catalog;
run
{
allocate channel ch1 type disk;
backup as backupset tag=orcl_L1 (database);
sql 'alter system archive log current';
release channel ch1;
allocate channel ch1 type disk;
backup
archivelog all
delete all input;
release channel ch1;
}
Step 7
=======
set ORACLE_SID=ORCL –-your target or source database
rman target / nocatalog auxiliary sys/oracle@orcl2 –orcl2 is the aux db
Step 8
=======
run
{
allocate auxiliary channel aux1 type disk;
set newname for datafile 1 to 'C:\oradata\ORCL2\SYSTEM01.DBF';
set newname for datafile 2 to 'C:\oradata\ORCL2\UNDOTBS01.DBF';
set newname for datafile 3 to 'C:\oradata\ORCL2\SYSAUX01.DBF';
set newname for datafile 4 to 'C:\oradata\ORCL2\USERS01.DBF';
duplicate target database to 'ORCL2' NOFILENAMECHECK;
}
RMAN responses as follows:
RMAN> run
2> {
3> allocate auxiliary channel aux1 type disk;
4> set newname for datafile 1 to 'C:\oradata\ORCL2\SYSTEM01.DBF';
5> set newname for datafile 2 to 'C:\oradata\ORCL2\UNDOTBS01.DBF';
6> set newname for datafile 3 to 'C:\oradata\ORCL2\SYSAUX01.DBF';
7> set newname for datafile 4 to 'C:\oradata\ORCL2\USERS01.DBF';
8> duplicate target database to 'ORCL2' NOFILENAMECHECK;
9> }
allocated channel: aux1
channel aux1: sid=155 devtype=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting Duplicate Db at 20-MAY-07
contents of Memory Script:
{
set until scn 188601;
set newname for datafile 1 to
"C:\ORADATA\ORCL2\SYSTEM01.DBF";
set newname for datafile 2 to
"C:\ORADATA\ORCL2\UNDOTBS01.DBF";
set newname for datafile 3 to
"C:\ORADATA\ORCL2\SYSAUX01.DBF";
set newname for datafile 4 to
"C:\ORADATA\ORCL2\USERS01.DBF";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 20-MAY-07
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORADATA\ORCL2\SYSTEM01.DBF
restoring datafile 00002 to C:\ORADATA\ORCL2\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORADATA\ORCL2\SYSAUX01.DBF
restoring datafile 00004 to C:\ORADATA\ORCL2\USERS01.DBF
channel aux1: reading from backup piece C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_20\O1_MF_NNNDF_ORCL_L1_35
HN_.BKP
channel aux1: restored backup piece 1
piece handle=C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_20\O1_MF_NNNDF_ORCL_L1_3515NZHN_.BKP tag=ORCL_L1
channel aux1: restore complete, elapsed time: 00:00:55
Finished restore at 20-MAY-07
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL2" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 5 M ,
GROUP 2 SIZE 5 M ,
GROUP 3 SIZE 5 M
DATAFILE
'C:\ORADATA\ORCL2\SYSTEM01.DBF'
CHARACTER SET UTF8
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=623108765 filename=C:\ORADATA\ORCL2\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=623108765 filename=C:\ORADATA\ORCL2\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=623108765 filename=C:\ORADATA\ORCL2\USERS01.DBF
contents of Memory Script:
{
set until scn 188601;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 20-MAY-07
starting media recovery
channel aux1: starting archive log restore to default destination
channel aux1: restoring archive log
archive log thread=1 sequence=14
channel aux1: restoring archive log
archive log thread=1 sequence=15
channel aux1: reading from backup piece C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_20\O1_MF_ANNNN_TAG2007052
4354_3515QDDP_.BKP
channel aux1: restored backup piece 1
piece handle=C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_20\O1_MF_ANNNN_TAG20070520T214354_3515QDDP_.BKP tag=
0070520T214354
channel aux1: restore complete, elapsed time: 00:00:03
archive log filename=C:\FLASH_RECOVERY_AREA\ORCL2\ARCHIVELOG\2007_05_20\O1_MF_1_14_3515VMG5_.ARC thread=1 seque
14
channel clone_default: deleting archive log(s)
archive log filename=C:\FLASH_RECOVERY_AREA\ORCL2\ARCHIVELOG\2007_05_20\O1_MF_1_14_3515VMG5_.ARC recid=2 stamp=
08772
archive log filename=C:\FLASH_RECOVERY_AREA\ORCL2\ARCHIVELOG\2007_05_20\O1_MF_1_15_3515VMM1_.ARC thread=1 seque
15
channel clone_default: deleting archive log(s)
archive log filename=C:\FLASH_RECOVERY_AREA\ORCL2\ARCHIVELOG\2007_05_20\O1_MF_1_15_3515VMM1_.ARC recid=1 stamp=
08771
media recovery complete, elapsed time: 00:00:02
Finished recover at 20-MAY-07
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 167772160 bytes
Fixed Size 1246852 bytes
Variable Size 62916988 bytes
Database Buffers 100663296 bytes
Redo Buffers 2945024 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL2" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 5 M ,
GROUP 2 SIZE 5 M ,
GROUP 3 SIZE 5 M
DATAFILE
'C:\ORADATA\ORCL2\SYSTEM01.DBF'
CHARACTER SET UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"C:\ORADATA\ORCL\TEMP01.DBF";
switch clone tempfile all;
catalog clone datafilecopy "C:\ORADATA\ORCL2\UNDOTBS01.DBF";
catalog clone datafilecopy "C:\ORADATA\ORCL2\SYSAUX01.DBF";
catalog clone datafilecopy "C:\ORADATA\ORCL2\USERS01.DBF";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to C:\ORADATA\ORCL\TEMP01.DBF in control file
cataloged datafile copy
datafile copy filename=C:\ORADATA\ORCL2\UNDOTBS01.DBF recid=1 stamp=623109564
cataloged datafile copy
datafile copy filename=C:\ORADATA\ORCL2\SYSAUX01.DBF recid=2 stamp=623109564
cataloged datafile copy
datafile copy filename=C:\ORADATA\ORCL2\USERS01.DBF recid=3 stamp=623109565
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=623109564 filename=C:\ORADATA\ORCL2\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=623109564 filename=C:\ORADATA\ORCL2\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=623109565 filename=C:\ORADATA\ORCL2\USERS01.DBF
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 20-MAY-07
Backup RMAN catalog
=================================
Create an offline database backup , after your RMAN backups.
