ORACLE数据库备份恢复
一．
1.备份生产数据库
在线备份：
采用ARCHIVELOG mode 方式备份，可以脱机或联机备份。需要备份数据库和归档日志
$ . oraenv
ORACLE_SID = [prim] ? PRIM
$ rman target /
RMAN> backup database format '/tmp/db_%U';
RMAN> backup archivelog all format '/tmp/archs_%U';
RMAN> backup current controlfile format '/tmp/control.bks';
RMAN> backup spfile format '/tmp/spfile.bks';
离线备份：
在NOARCHIVELOG mode下备份，数据库在MOUNT下备份：
$ . oraenv
ORACLE_SID = [prim] ? PRIM
$ rman target /
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> backup database format '/tmp/db_%U';
RMAN> backup current controlfile format '/tmp/control.bks';
RMAN> backup spfile format '/tmp/spfile.bks';
- 拷贝备份文件到新的数据库
- 恢复新的数据库，
Perform these steps at the new host...
Step 1.3.1) Set the environment to point to the ORACLE_SID that you are about to restore:
设置环境指向你要恢复的ORACLE_SID:
$ . oraenv
ORACLE_SID = [prim] ? PRIM
Step 1.3.2) Restore the spfile from the backuppiece:
RMAN> startup nomount force;
RMAN> restore spfile from '/tmp/spfile.bks';
RMAN> restore spfile to pfile '/tmp/initnewdb.ora' from '/tmp/spfile.bks';
Step 1.3.3) Check the init.ora parameters, and precreate the directories if required. Note that the AUDIT directory must pre-exist before you can NOMOUNT the restored spfile
检查初始化。Ora参数，并根据需要预创建目录。注意，在NOMOUNT恢复的spfile之前，AUDIT目录必须预先存在
$ grep audit /tmp/initnewdb.ora
*.audit_file_dest='/<path>/admin/PRIM/adump'
$ mkdir -p /<path>/admin/PRIM/adump
SQL> shutdown immediate;
SQL> startup nomount;
SQL> show parameter control_files
SQL> show parameter dump
SQL> show parameter create
SQL> show parameter recovery
Step 1.3.4) Restore the controlfile from a known backuppiece and mount the database:
RMAN> restore controlfile from '/tmp/control.bks';
RMAN> alter database mount;
RMAN> report schema;
If the backuppieces are residing in a new location at the new host you'll need to catalog them:
如果备份位于新主机的新位置，则需要对它们进行编目:
RMAN> catalog start with '/<path for backuppiece location>/';
Step 1.3.5) Restore the database, using SET NEWNAME to relocate the datafiles and tempfiles to their new locations if required.
恢复数据库，如果需要，使用SET NEWNAME将数据文件和tempfile重新定位到它们的新位置。
- preview the restore - this will report the backuppieces required for the restore operation. It will not perform the actual restore.
A)预览恢复—这将报告恢复操作所需的备份。它不会执行实际的恢复
RMAN> restore database preview summary;
b) If the preview looks valid, then go ahead and perform the actual restore to the new host:
如果预览看起来有效，那么继续执行实际的恢复到新主机:
RMAN> run {
# set newname for all datafiles to be mapped to a new path
# OR use SET NEWNAME FOR DATABASE if you wish to have all files located in the same directory
# eg. SET NEWNAME FOR DATABASE to '+DATA/inovadg/datafile/%b'
set newname for datafile 1 to 'new file path and name';
...
set newname for tempfile 1 to 'new file path and name';
restore database;
switch datafile all;
switch tempfile all;
}
Step 1.3.6) Confirm that all datafiles have been restored to the new location:
确认所有数据文件已恢复到新位置:
RMAN> report schema;
1.3.7) Recover the database
a) if recovering from an OFFLINE backup, and you do not have further archivelogs to apply from the original host, use recover with NOREDO:
如果从离线备份中恢复，并且您没有从原始主机申请更多的归档文件，请使用recover和NOREDO:
RMAN> recover database noredo;
b) if the original database is running in archivelog mode and there are subsequent archivelogs generated after the initial backup, you can back up these archivelogs and transfer them to the new host as well.
At the original host:
如果原始数据库以archivelog模式运行，并且在初始备份之后生成了后续的归档日志，那么可以备份这些归档日志并将它们传输到新主机上。
RMAN> backup archivelog all format '/tmp/rest_of_arc_%U.bks;
At the new host, catalog this new backuppiece:
RMAN> catalog backuppiece '/tmp/rest_of_arc';
Now recover the database:
RMAN> run {# change the date and time to suit
SET UNTIL TIME "to_date('01 SEP 2011 12:04:00','DD MON YYYY hh24:mi:ss')";
recover database;
}
Step 1.3.8) Relocate all the online redo logs if required:
如果需要，重新定位所有在线重做日志:
SQL> select * from v$logfile;
SQL> alter database rename file '<old redo log path and name>' to '<new redo log path and name>';
Step 1.3.9) Once all files have been renamed, open the database with resetlogs.
重命名所有文件后，使用resetlogs打开数据库。
a) if the source database had block change tracking, you will need to either precreate the block change tracking directory, or disable and enable it before opening the database:
如果源数据库有块更改跟踪，您将需要预先创建块更改跟踪目录，或者在打开数据库之前禁用并启用它:
SQL> alter database disable block change tracking;
SQL> alter database enable block change tracking using '<location>';
b) now open with resetlogs:
RMAN> alter database open resetlogs;
Step 1.3.10) confirm the location of your tempfiles, recreating them at the new location if required:
确认你的tempfile的位置，如果需要，在新的位置重新创建它们:
SQL> select * from v$tempfile;
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'new tempfile path filename' REUSE;
Step 1.3.11) If the old database is going to remain up and running on the old server, use NID to rename the DBNAME and DBID of either the old or new database to avoid confusion.
如果旧数据库将继续在旧服务器上运行，请使用NID重命名旧数据库或新数据库的DBNAME和DBID，以避免混淆。
For example:
$ nid target=sys/<password> dbname=TEST
For further information about NID, see the following note:
How to Change the DBID, DBNAME Using NID Utility in version 10GgR2 onwards (Doc ID 863800.1)
注意恢复报错处理：
redo日志文件丢失恢复
https://blog.csdn.net/shineboy123456/article/details/123821733
https://www.cnblogs.com/storymedia/p/4538871.html
1、查看redo日志文件前状态：
SQL> select * from v$log;
- 删除redo日志文件组1（模拟丢失）:
rm -f /u01/app/oracle/oradata/CDB1/redo01.log
3、用CLEAR命令重建该日志文件（mount状态下执行）
SQL>alter database clear logfile group 1;
如果是该日志组还没有归档，则需要用
SQL>alter database clear unarchived logfile group 1;
如果损坏的重做日志文件尚未归档，请在语句中使用
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
Solution 2 - Detailed Steps With Example Output
Step 2.1 - Backup the production database
$rman target / log=backup.log
RMAN>run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
backup format '<give location of backup>/%U' filesperset 4 (database);
sql 'alter system archive log current';
backup format '<give location of backup>/%U' filesperset 8 (archivelog all);
backup format '<give location of backup>/%U' current controlfile;
}
Example
-----------
Script
-------
rman target / log=backup.log
RMAN>run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
backup format 'D:\backup\prim\%U' filesperset 4 (database);
backup format 'D:\backup\prim\%U' filesperset 8 (archivelog all);
backup format 'D:\backup\prim\%U' current controlfile;
}
Backup log
--------------
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jan 17 16:21:15 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIM (DBID=2610619323)
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=192 device type=DISK
allocated channel: t2
channel t2: SID=10 device type=DISK
allocated channel: t3
channel t3: SID=68 device type=DISK
allocated channel: t4
channel t4: SID=134 device type=DISK
Starting backup at 17-JAN-12
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=\<path>\PRIM\SYSTEM01.DBF
channel t1: starting piece 1 at 17-JAN-12
channel t2: starting full datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00002 name=<path>\PRIM\SYSAUX01.DBF
channel t2: starting piece 1 at 17-JAN-12
channel t3: starting full datafile backup set
channel t3: specifying datafile(s) in backup set
input datafile file number=00003 name=<path>\PRIM\UNDOTBS01.DBF
channel t3: starting piece 1 at 17-JAN-12
channel t4: starting full datafile backup set
channel t4: specifying datafile(s) in backup set
input datafile file number=00004 name=<path>\PRIM\USERS01.DBF
channel t4: starting piece 1 at 17-JAN-12
channel t3: finished piece 1 at 17-JAN-12
piece handle=D:\BACKUP\PRIM\0UN10JGO_1_1 tag=TAG20120117T162144 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:01
channel t4: finished piece 1 at 17-JAN-12
piece handle=D:\BACKUP\PRIM\0VN10JGP_1_1 tag=TAG20120117T162144 comment=NONE
channel t4: backup set complete, elapsed time: 00:00:01
channel t1: finished piece 1 at 17-JAN-12
piece handle=D:\BACKUP\PRIM\0SN10JGO_1_1 tag=TAG20120117T162144 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:26
channel t2: finished piece 1 at 17-JAN-12
piece handle=D:\BACKUP\PRIM\0TN10JGO_1_1 tag=TAG20120117T162144 comment=NONE
channel t2: backup set complete, elapsed time: 00:00:26
Finished backup at 17-JAN-12
Starting Control File and SPFILE Autobackup at 17-JAN-12
piece handle=<oracle_home path>\DATABASE\C-2610619323-20120117-03 comment=NONE
Finished Control File and SPFILE Autobackup at 17-JAN-12
Starting backup at 17-JAN-12
current log archived
channel t1: starting archived log backup set
channel t1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=14 STAMP=772567403
input archived log thread=1 sequence=16 RECID=15 STAMP=772617742
channel t1: starting piece 1 at 17-JAN-12
channel t2: starting archived log backup set
channel t2: specifying archived log(s) in backup set
input archived log thread=1 sequence=17 RECID=16 STAMP=772658562
input archived log thread=1 sequence=18 RECID=17 STAMP=772704521
input archived log thread=1 sequence=19 RECID=18 STAMP=772710784
channel t2: starting piece 1 at 17-JAN-12
channel t3: starting archived log backup set
channel t3: specifying archived log(s) in backup set
input archived log thread=1 sequence=20 RECID=19 STAMP=772738880
input archived log thread=1 sequence=21 RECID=20 STAMP=772790482
input archived log thread=1 sequence=22 RECID=21 STAMP=772820536
channel t3: starting piece 1 at 17-JAN-12
channel t4: starting archived log backup set
channel t4: specifying archived log(s) in backup set
input archived log thread=1 sequence=23 RECID=22 STAMP=772820536
channel t4: starting piece 1 at 17-JAN-12
channel t4: finished piece 1 at 17-JAN-12
piece handle=D:\BACKUP\PRIM\14N10JHQ_1_1 tag=TAG20120117T162217 comment=NONE
channel t4: backup set complete, elapsed time: 00:00:01
channel t1: finished piece 1 at 17-JAN-12
piece handle=D:\BACKUP\PRIM\11N10JHQ_1_1 tag=TAG20120117T162217 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:07
channel t2: finished piece 1 at 17-JAN-12
piece handle=D:\BACKUP\PRIM\12N10JHQ_1_1 tag=TAG20120117T162217 comment=NONE
channel t2: backup set complete, elapsed time: 00:00:08
channel t3: finished piece 1 at 17-JAN-12
piece handle=D:\BACKUP\PRIM\13N10JHQ_1_1 tag=TAG20120117T162217 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:08
Finished backup at 17-JAN-12
Starting Control File and SPFILE Autobackup at 17-JAN-12
piece handle=<oracle_home path>\DATABASE\C-2610619323-20120117-04 comment=NONE
Finished Control File and SPFILE Autobackup at 17-JAN-12
Starting backup at 17-JAN-12
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
including current control file in backup set
channel t1: starting piece 1 at 17-JAN-12
channel t1: finished piece 1 at 17-JAN-12
piece handle=D:\BACKUP\PRIM\16N10JI7_1_1 tag=TAG20120117T162231 comment=NONE <channel t1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-JAN-12
Starting Control File and SPFILE Autobackup at 17-JAN-12
piece handle=<oracle_home path>\DATABASE\C-2610619323-20120117-05 comment=NONE
Finished Control File and SPFILE Autobackup at 17-JAN-12
released channel: t1
released channel: t2
released channel: t3
released channel: t4
Step 2.2 - Transfer the backuppieces to the new host
Transfer the backup pieces from production to the non-production server. Prior to 10g you must locate them in the same directory path. From 10g and onwards we can catalog backuppieces so this is no longer a requirement.
You will also need to bring across the database parameter file.
Henceforth all steps are performed at the new host...
Step 2.2.1 This steps are specific to WINDOWS
- Create an Oracle Password File ( orapwd )
- Create an Initialization Parameter File / or have it copied and adjusted from source database
- Create the Oracle Services (oradim)
1: Create an Oracle Password File
-------------------------------------------------------------
For full details on how to create a password file please refer to
Oracle Database Administrator's Guide.
http://docs.oracle.com/cd/E11882_01/server.112/e25494/dba.htm#ADMIN001
For example: C:\> ORAPWD file=PWD<ORACLE_SID>.ora password=<password> entries=10
Note:
ORADIM does create a passwordfile if used like in this example in step 3
C:\> ORADIM -new -sid ORACLE_SID -intpwd <password> -maxusers 10 -startmode auto -pfile '<your pfile location>'
2: Create an Initialization Parameter File
----------------------------------------------------------------------
Restore the spfile from the backup, and if you don't have the init.ora
you can use an init.ora from another database and make the necessary changes
You need setup the required parameters e.g DB_NAME, CONTROL_FILES and
directories for bdump, udump,cdump etc...
i.e. of restoring spfile
RMAN> restore spfile to pfile '/tmp/init<sidname>.ora' from '/<backup_location>/<backup_piece_name>';
Parameter file '<ORACLE_HOME>\DATABASE\init<ORACLE_SID>.ORA'
3: Create the Oracle services
--------------------------------
Create a new NT service for the duplicate database TEST using oradim.
As Guideline please see
(Doc ID 114384.1) WIN: Checklist for Resolving CONNECT AS SYSDBA Issues
C:\> ORADIM -new -sid ORACLE_SID -intpwd <password> -maxusers 10 -startmode auto -pfile '<your pfile location>'
Step 2.3 - Restore the controlfile
Change the pfile related to controlfile location,dump directories etc
Startup in nomount restore the controlfile:
$rman target /
RMAN> restore controlfile from '<backup piece name of controlfile ie last step of backup >';
RMAN> alter database mount;
Example
----------
Here onward we will see at source we took backup to location at "D:\BACKUP\PRIM\" but we copied backup at "D:\BACKUP\TEST" at new server
RMAN> restore controlfile from 'D:\BACKUP\TEST\16N10JI7_1_1';
Starting restore at 17-JAN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=66 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=<path>\TEST\CONTROL01.CTL
output file name=<path>\TEST_2\CONTROL02.CTL
Finished restore at 17-JAN-12
RMAN>
Step 2.4 - Catalog the backuppieces if required
If backup pieces have been copied to a different location than source server then we need to catalog those backuppieces (only applicable to 10g and above)
+ RMAN> catalog start with '<new location>' noprompt;
(this command will add all backup pieces in metadata and now onward we can see 2 copies of backup in metadata )
+ RMAN> crosscheck backup tag '<backup tag from backup log>' ;
(By default oracle picks latest backup tag and the first copy if multiple copies exist,to avoid conflict or backup piece not found error (as we kept backup pieces in different location) ,run crosscheck command so that original location backup pieces ie first copy will be marked as expired )
+ RMAN> delete expired backup;
(delete first copy of backup piece)
Example
---------
RMAN> catalog start with 'D:\BACKUP\TEST\' noprompt;
searching for all files that match the pattern D:\BACKUP\TEST\
List of Files Unknown to the Database
=====================================
File Name: D:\BACKUP\TEST\0SN10JGO_1_1
File Name: D:\BACKUP\TEST\0TN10JGO_1_1
File Name: D:\BACKUP\TEST\0UN10JGO_1_1
File Name: D:\BACKUP\TEST\0VN10JGP_1_1
File Name: D:\BACKUP\TEST\11N10JHQ_1_1
File Name: D:\BACKUP\TEST\12N10JHQ_1_1
File Name: D:\BACKUP\TEST\13N10JHQ_1_1
File Name: D:\BACKUP\TEST\14N10JHQ_1_1
File Name: D:\BACKUP\TEST\16N10JI7_1_1
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: D:\BACKUP\TEST\0SN10JGO_1_1
File Name: D:\BACKUP\TEST\0TN10JGO_1_1
File Name: D:\BACKUP\TEST\0UN10JGO_1_1
File Name: D:\BACKUP\TEST\0VN10JGP_1_1
File Name: D:\BACKUP\TEST\11N10JHQ_1_1
File Name: D:\BACKUP\TEST\12N10JHQ_1_1
File Name: D:\BACKUP\TEST\13N10JHQ_1_1
File Name: D:\BACKUP\TEST\14N10JHQ_1_1
File Name: D:\BACKUP\TEST\16N10JI7_1_1
RMAN> list backup of datafile 1;
List of Backup Sets
===================
BS Key Type LV Size
------- ---- -- ----------
25 Full 585.05M
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1498600 17-JAN-12 <path>\PRIM\SYSTEM01.DBF
Backup Set Copy #1 of backup set 25
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:20 17-JAN-12 NO TAG20120117T162144
List of Backup Pieces for backup set 25 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
25 1 AVAILABLE D:\BACKUP\PRIM\0SN10JGO_1_1
Backup Set Copy #2 of backup set 25
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:20 17-JAN-12 NO TAG20120117T162144
List of Backup Pieces for backup set 25 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
32 1 AVAILABLE D:\BACKUP\TEST\0SN10JGO_1_1
RMAN>
RMAN> crosscheck backup;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=66 device type=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\BACKUP\PRIM\0UN10JGO_1_1 RECID=22 STAMP=772820505
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\BACKUP\TEST\0UN10JGO_1_1 RECID=34 STAMP=772821367
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\BACKUP\PRIM\0VN10JGP_1_1 RECID=23 STAMP=772820505
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\BACKUP\TEST\0VN10JGP_1_1 RECID=35 STAMP=772821367
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\BACKUP\PRIM\0TN10JGO_1_1 RECID=24 STAMP=772820505
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\BACKUP\TEST\0TN10JGO_1_1 RECID=33 STAMP=772821366
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\BACKUP\PRIM\0SN10JGO_1_1 RECID=25 STAMP=772820504
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\BACKUP\TEST\0SN10JGO_1_1 RECID=32 STAMP=772821366
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\<oracle_home path>\DATABASE\C-2610619323-20120117-03 RECID=26 STAMP=772820531
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\BACKUP\PRIM\14N10JHQ_1_1 RECID=27 STAMP=772820538
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\BACKUP\TEST\14N10JHQ_1_1 RECID=39 STAMP=772821368
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\BACKUP\PRIM\13N10JHQ_1_1 RECID=28 STAMP=772820538
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\BACKUP\TEST\13N10JHQ_1_1 RECID=38 STAMP=772821368
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\BACKUP\PRIM\12N10JHQ_1_1 RECID=29 STAMP=772820538
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\BACKUP\TEST\12N10JHQ_1_1 RECID=37 STAMP=772821367
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\BACKUP\PRIM\11N10JHQ_1_1 RECID=30 STAMP=772820538
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\BACKUP\TEST\11N10JHQ_1_1 RECID=36 STAMP=772821367
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=<oracle_home path>\DATABASE\C-2610619323-20120117-04 RECID=31 STAMP=772820547
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\BACKUP\TEST\16N10JI7_1_1 RECID=40 STAMP=772821368
Crosschecked 40 objects
RMAN> delete expired backup tag 'TAG20120117T162144';
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
22 22 1 1 EXPIRED DISK D:\BACKUP\PRIM\0UN10JGO_1_1
23 23 1 1 EXPIRED DISK D:\BACKUP\PRIM\0VN10JGP_1_1
24 24 1 1 EXPIRED DISK D:\BACKUP\PRIM\0TN10JGO_1_1
25 25 1 1 EXPIRED DISK D:\BACKUP\PRIM\0SN10JGO_1_1
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=D:\BACKUP\PRIM\0UN10JGO_1_1 RECID=22 STAMP=772820505
deleted backup piece
backup piece handle=D:\BACKUP\PRIM\0VN10JGP_1_1 RECID=23 STAMP=772820505
deleted backup piece
backup piece handle=D:\BACKUP\PRIM\0TN10JGO_1_1 RECID=24 STAMP=772820505
deleted backup piece
backup piece handle=D:\BACKUP\PRIM\0SN10JGO_1_1 RECID=25 STAMP=772820504
Deleted 4 EXPIRED objects
RMAN> delete expired backup tag 'TAG20120117T162217';
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
27 27 1 1 EXPIRED DISK D:\BACKUP\PRIM\14N10JHQ_1_1
28 28 1 1 EXPIRED DISK D:\BACKUP\PRIM\13N10JHQ_1_1
29 29 1 1 EXPIRED DISK D:\BACKUP\PRIM\12N10JHQ_1_1
30 30 1 1 EXPIRED DISK D:\BACKUP\PRIM\11N10JHQ_1_1
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=D:\BACKUP\PRIM\14N10JHQ_1_1 RECID=27 STAMP=772820538
deleted backup piece
backup piece handle=D:\BACKUP\PRIM\13N10JHQ_1_1 RECID=28 STAMP=772820538
deleted backup piece
backup piece handle=D:\BACKUP\PRIM\12N10JHQ_1_1 RECID=29 STAMP=772820538
deleted backup piece
backup piece handle=D:\BACKUP\PRIM\11N10JHQ_1_1 RECID=30 STAMP=772820538
Deleted 4 EXPIRED objects
RMAN> list backup of datafile 1;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 585.05M DISK 00:00:20 17-JAN-12
BP Key: 32 Status: AVAILABLE Compressed: NO Tag: TAG20120117T162144
Piece Name: D:\BACKUP\TEST\0SN10JGO_1_1
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1498600 17-JAN-12 <path>\PRIM\SYSTEM01.DBF
RMAN>
Step 2.5 - Restore the database
Check the datafile locations:
RMAN> report schema;
Example.
RMAN> report schema;
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name PRIM
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** <path>\PRIM\SYSTEM01.DBF
2 512 SYSAUX *** <path>\PRIM\SYSAUX01.DBF
3 30 UNDOTBS1 *** <path>\PRIM\UNDOTBS01.DBF
4 5 USERS *** <path>\PRIM\USERS01.DBF
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 <path>\PRIM\TEMP01.DBF
RMAN>
If you need to the datafiles to be restored to a new location on the new host, you can use SET NEWNAME:
RMAN> set newname for datafile x to '<new location and name>';
script will look like this
$rman target / log=restore.log
RMAN> run{
allocate channel t1 type disk;
allocate channel t2 type disk;
set newname for datafile 1 to '<path>\TEST\ORADATA\SYSTEM01.DBF';
set newname for datafile 2 to '<path>\TEST\ORADATA\SYSAUX01.DB';
set newname for datafile 3 to '<path>\TEST\ORADATA\UNDOTBS01.DBF';
set newname for datafile 4 to '<path>\TEST\ORADATA\USERS01.DBF';
set until sequence 24;
restore database;
switch datafile all;
recover database;
}
Note# you can get the max sequence number in 2 ways
1) select max(sequence#) from v$backup_redolog OR
2) refer to your RMAN backup log and see the max sequence number backed up
after completion of above step db will be restored and recovered.
Example
---------
RMAN> run{
2> allocate channel t1 type disk;
3> allocate channel t2 type disk;
4> set newname for datafile 1 to '<path>\TEST\ORADATA\SYSTEM01.DBF';
5> set newname for datafile 2 to '<path>\TEST\ORADATA\SYSAUX01.DBF';
6> set newname for datafile 3 to '<path>\TEST\ORADATA\UNDOTBS01.DBF';
7> set newname for datafile 4 to '<path>\TEST\ORADATA\USERS01.DBF';
8> set until sequence 24;
9> restore database;
10> switch datafile all;
11> recover database;
12> }
released channel: ORA_DISK_1
allocated channel: t1
channel t1: SID=66 device type=DISK
allocated channel: t2
channel t2: SID=129 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET until clause
Starting restore at 17-JAN-12
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00003 to <path>\TEST\ORADATA\UNDOTBS01.DBF
channel t1: reading from backup piece D:\BACKUP\TEST\0UN10JGO_1_1
channel t2: starting datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
channel t2: restoring datafile 00004 to <path>\TEST\ORADATA\USERS01.DBF
channel t2: reading from backup piece D:\BACKUP\TEST\0VN10JGP_1_1
channel t2: piece handle=D:\BACKUP\TEST\0VN10JGP_1_1 tag=TAG20120117T162144
channel t2: restored backup piece 1
channel t2: restore complete, elapsed time: 00:00:01
channel t2: starting datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
channel t2: restoring datafile 00002 to <path>\TEST\ORADATA\SYSAUX01.DBF
channel t2: reading from backup piece D:\BACKUP\TEST\0TN10JGO_1_1
channel t1: piece handle=D:\BACKUP\TEST\0UN10JGO_1_1 tag=TAG20120117T162144
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:05
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00001 to <path>\TEST\ORADATA\SYSTEM01.DBF
channel t1: reading from backup piece D:\BACKUP\TEST\0SN10JGO_1_1
channel t1: piece handle=D:\BACKUP\TEST\0SN10JGO_1_1 tag=TAG20120117T162144
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:45
channel t2: piece handle=D:\BACKUP\TEST\0TN10JGO_1_1 tag=TAG20120117T162144
channel t2: restored backup piece 1
channel t2: restore complete, elapsed time: 00:00:48
Finished restore at 17-JAN-12
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=772824921 file name=<path>\TEST\ORADATA\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=772824921 file name=<path>\TEST\ORADATA\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=772824922 file name=<path>\TEST\ORADATA\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=772824922 file name=<path>\TEST\ORADATA\USERS01.DBF
Starting recover at 17-JAN-12
starting media recovery
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=22
channel t1: reading from backup piece D:\BACKUP\TEST\13N10JHQ_1_1
channel t2: starting archived log restore to default destination
channel t2: restoring archived log
archived log thread=1 sequence=23
channel t2: reading from backup piece D:\BACKUP\TEST\14N10JHQ_1_1
channel t1: piece handle=D:\BACKUP\TEST\13N10JHQ_1_1 tag=TAG20120117T162217
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
archived log file name=<path>\ARC0000000022_0772204606.0001 thread=1 sequence=22
channel t2: piece handle=D:\BACKUP\TEST\\14N10JHQ_1_1 tag=TAG20120117T162217
channel t2: restored backup piece 1
channel t2: restore complete, elapsed time: 00:00:01
archived log file name=<path>\ARC0000000023_0772204606.0001 thread=1 sequence=23
media recovery complete, elapsed time: 00:00:01
Finished recover at 17-JAN-12
released channel: t1
released channel: t2
RMAN>
Step 2.6 - Rename the online redo logs
Before opening database please make sure online redo logs are in the appropriate location:
SQL> set lines 200
col member format a60
select a.thread#,a.group#,b.type,b.member,a.bytes/1048576
from v$log a,v$logfile b
where a.group#=b.group# order by a.group#;
If you want to rename the location and name:
SQL> alter database rename file '<old file location and name>' to '<new location and name>';
Example
----------
SQL> set lines 200
SQL> col member format a60
SQL> select a.thread#,a.group#,b.type,b.member,a.bytes/1048576 from v$log a,v$logfile b where a.group#=b.group# order by a.group#;
THREAD# GROUP# TYPE MEMBER A.BYTES/1048576
---------- ---------- ------- ------------------------------------------------------------ ---------------
1 1 ONLINE <path>\PRIM\REDO01.LOG 50
1 2 ONLINE <path>\PRIM\REDO02.LOG 50
1 3 ONLINE <path>\PRIM\REDO03.LOG 50
SQL> alter database rename file '<path>\PRIM\REDO01.LOG' to '<path>\TEST\ORADATA\REDO01.LOG';
Database altered.
SQL> alter database rename file '<path>\PRIM\\REDO02.LOG' to '<path>\TEST\ORADATA\REDO02.LOG';
Database altered.
SQL> alter database rename file '<path>\PRIM\\REDO03.LOG' to '<path>\TEST\ORADATA\REDO03.LOG';
Database altered.
Now confirm the new location of redo
SQL> select a.thread#,a.group#,b.type,b.member,a.bytes/1048576 from v$log a,v$logfile b where a.group#=b.group# order by a.group#;
THREAD# GROUP# TYPE MEMBER A.BYTES/1048576
---------- ---------- ------- ------------------------------------------------------------ ---------------
1 1 ONLINE <path>\TEST\ORADATA\REDO01.LOG 50
1 2 ONLINE <path>\TEST\ORADATA\REDO02.LOG 50
1 3 ONLINE <path>\TEST\ORADATA\REDO03.LOG 50
SQL>
Now open database with resetlogs;
SQL> alter database open resetlogs;
Step 2.7 - Rename the database
The newly restored database will have the same DBID and DB_NAME as the original database so we will need to change them.
You can use NID:
How to Change the DBID, DBNAME Using NID Utility in version 10gR2 onwards (Document ID 863800.1)
Or recreate the controlfile as follows...
Take a controlfile trace:
SQL> alter database backup controlfile to trace as '<location and name of trace file>';
SQL> oradebug setmypid;
SQL> oradebug tracefile_name;
Once the trace file is generated do graceful shutdown:
SQL> shutdown immediate;
Edit the trace file generated above, and change this line:
CREATE CONTROLFILE REUSE DATABASE "PRIM" NORESETLOGS ARCHIVELOG ====>assuming production database name is PRIM
to
CREATE CONTROLFILE set DATABASE "TEST" RESETLOGS ARCHIVELOG FORCE LOGGING ====>assuming Non-Prod database name is TEST
The script will look similar to this
CREATE CONTROLFILE set DATABASE "TEST" RESETLOGS ARCHIVELOG FORCE LOGGING
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/<path>/TEST/redo01.log' SIZE 50M,
GROUP 2 '/<path>/TEST/redo02.log' SIZE 50M,
GROUP 3 '/<path>/TEST/redo03.log' SIZE 50M
DATAFILE
'/<path>/TEST/system01.dbf',
'/<path>/TEST/sysaux01.dbf',
'/<path>/TEST/undotbs01.dbf'
'/<path>/TEST/user01.dbf'
CHARACTER SET WE8MSWIN1252
;
Now set the sid as per new sid name
set ORACLE_SID=TEST
(assuming TEST is the new SID name)
+assuming pfile has been created for DB TEST
put the database in nomount stage
SQL> startup nomount;
==>run the script to re-create controlfile
SQL> alter database open resetlogs;
Step 2.8 - Confirm your tempfiles
Confirm the location of your tempfiles, recreate them at the new location if required:
SQL> select * from v$tempfile;
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'new tempfile path filename' REUSE;
