?sample 1:?
1.清空歸檔日志
RMAN> crosscheck archivelog all;
RMAN> delete achivelog all;
?
2.清空數據文件。
select name from v$datafile;
rm v$datafile
?
3.恢復數據
??##check file date:??
##把db數據恢復到：2017-02-05 00:00:00
?ls -lt /ngenprdblog/
?ls -lt /ngprdb/data
?
?##change data file location to '/ngprdb/data'
?rman nocatalog target /
?catalog start with '/ngprdb/data';
?switch database to copy;
?
(解釋如下)
現在換個角度來考慮，這些控制文件記錄的都是不存在的文件，只是數據庫認為是這些文件，而我的ASM上才是真實的數據文件，那么，
我可以把ASM上的文件都認為是數據文件的copy備份，完全可以使用RMAN的catalog start with手工將這些copy“備份”加進來，數據庫會認為這些文件就只是數據文件的copy備份，然后直接switch database to copy就可以成功切換。）
?### change logfile location to '/ngprdb/data'
?select 'alter database rename file '||''''||member||''''||' to '||chr(39)||replace(member,'/ngenprdb/','/ngprdb/')||''';'? ?
from v$logfile;
?###recover database method 1
recover database;
sql 'alter database open';
?
?##recover database method 2
run{
set until time "to_date('2017-02-05 00:00:00','YYYY-MM-DD? HH24:MI:SS')";
restore database;
recover database;
sql 'alter database open resetlogs';
}
sampe 2 .restore prod db to uat db
initedruat.ora
change db_name
#db_name = edruat
db_name = edrprod
dbngt6[/home/oracle][edruat] >sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue May 9 17:06:05 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 5678927872 bytes
Fixed Size 2215680 bytes
Variable Size 1006633216 bytes
Database Buffers 4664066048 bytes
Redo Buffers 6012928 bytes
Database mounted.
SQL> alter database backup controlfile to trace;
Database altered.
SQL> select * from v$diag_info;
initedruat.ora
change db_name
db_name = edruat
#db_name = edrprod
sed -n "1,2p"
sed -n "70,210p" /edruatdb/diag/rdbms/edrprod/edruat/trace/edruat_ora_8217.trc > /tmp/contrl.sql
set ic
%s/prod/uat/g
%s/PROD/UAT/g
###
ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name EDRPROD in file header does not match given name of
EDRUAT
ORA-01110: data file 1: '/edruatdb/data/system_f01.dbf'
#####
vi /tmp/contrl.sql
chang "resue" to "SET" and "NORESETLOGS" to "RESETLOGS"
CREATE CONTROLFILE SET DATABASE "DEV" RESETLOGS ARCHIVELOG
?
######
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: '/edruatdb/data/cntl01.ctl'
ORA-27038: created file already exists
Additional information: 1
######
mv /edruatdb/data/cntl01.ctl /edruatdb/data/cntl01.ctl.bak
mv /edruatdb/data/cntl02.ctl /edruatdb/data/cntl02.ctl.bak
?
SQL> @/tmp/contrl.sql
ORACLE instance started.
Total System Global Area 5678927872 bytes
Fixed Size 2215680 bytes
Variable Size 1006633216 bytes
Database Buffers 4664066048 bytes
Redo Buffers 6012928 bytes
Control file created.
?
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 9742867340450 generated at 05/08/2017 14:00:07 needed for
thread 1
ORA-00289: suggestion : /edruatdblog/edrprod_863435250_1_37704.arc
ORA-00280: change 9742867340450 for thread 1 is in sequence #37704
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 9742867342449 generated at 05/08/2017 14:43:31 needed for
thread 1
ORA-00289: suggestion : /edruatdblog/edrprod_863435250_1_37705.arc
ORA-00280: change 9742867342449 for thread 1 is in sequence #37705
ORA-00278: log file '/edruatdblog/edrprod_863435250_1_37704.arc' no longer
needed for this recovery
?
SQL> alter database open resetlogs;
Database altered.
cd /edruatdb/change/env
SQL> @chg_env_edruat.sql
?
?
rm /edruatdb/data/temp_f*
alter tablespace TEMP add tempfile '/edruatdb/data/temp_f01.dbf' size 4000m autoextend on;
alter tablespace TEMP add tempfile '/edruatdb/data/temp_f02.dbf' size 4000m autoextend on;
alter tablespace TEMP add tempfile '/edruatdb/data/temp_f03.dbf' size 4000m autoextend on;
alter database default temporary tablespace temp;
?
?
Solution
How to restore a complete database from an RMAN backup
This note assumes an RMAN catalog is not available. The use of a catalog is optional in this scenario as the backup information is available in the controlfile.
In this example we will assume all files are required to be restored:
* Datafiles
* Controlfiles
* Archivelogs (In order to perform recovery)
Online redo logs and temp files are recreated automatically by RMAN when a resetlogs is issued. Online redo logs and temp files are not backed up by RMAN
Step 1: ?Identify controlfile backup to restore
Note: If? you do not need to restore a controlfile proceed to step 3.
* Locate the RMAN backup you wish to restore.
* These files should be located in the directory where they were backed up to.
* If you have the RMAN backup log available this will also be of assistance.
Within the RMAN backup log you will see the controlfile is backed up last the the piece handle is shown.
....
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2009/01/01 12:00:00
channel ORA_DISK_1: finished piece 1 at 2009/01/01 12:00:02
piece handle=/recovery_area/V11/backupset/2009_05_0 /o1_mf_ncsnf_TAG20090506T11_501tr0h7_.bkp tag=TAG20090506T11 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
If you do not have an RMAN backup log simply locate the last file RMAN backed up.? This should contain the controlfile backup.
Step 2: ?Restore the controlfile
2a) If you DO NOT have a spfile.
If you do have an spfile or init.ora move to Step 2b
% rman target /
RMAN> startup nomount force;
You will see this message:
..
starting Oracle instance without parameter file for retrieval of spfile
..
At this point you can restore the spfile:
RMAN> restore spfile from '/recovery_area/V11/backupset/2009_05_05/o1_mf_ncsnf_TAG20_501tr0h7_.bkp';
RMAN> shutdown immediate;
Once the spfile has been successfully restored proceed to Step 2b.
2b)
Following?the?successful?nomount?of?the?instance?you?are?ready?to?restore?the
controlfile;
NOTE:?The?controlfile?will?be?restored?to?the?following?location:
SQL>?show?parameter?control_files
NAME?TYPE?VALUE
------------------------------------?-----------?------------------------------
control_files?string?/oradata/V11/control01.ctl
%?rman?target?/
You?will?see?the?message:
connected?to?target?database:?V11?(not?mounted)
RMAN> restore controlfile from '/recovery_area/V11/backupset/2009_05_06/o1_mf_ncsnf_TAG20090506T113947_501tr0h7_.bkp';
Starting restore at 2009/05/11 11:01:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=151 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/V11/control01.ctl
Finished restore at 2009/05/11 11:01:27
In this example the controlfile has been restored to '/oradata/V11/control01.ctl'
Step 3: ?Restore and recover the database
Your next task is to restore the database and perform recovery. ?Mount the database now that the controlfile has been restored:
Now you have two options for recovery.
1) Full/Complete recovery.
2) Point In Time Recovery (PIT)
In both examples it is assumed that all archivelogs are available to perform the recovery.
?
==========
To Perform a full restore and recovery.
run{
restore database;
recover database;
alter database open resetlogs;
}
If you performed a complete recovery with current controlfile and online redologs in place, you might get below error when opening the database with resetlogs:
ORA-01139: RESET LOGS option only valid after an incomplete database recovery
At this point simply open the database without resetlogs option.
===========
Point-In-Time Recovery (PITR) would be used if you have decided to restore a database to a particular point in time. This may be warranted for a hardware fault or if you are aware of a database corruption that occured at a certain date/time.
run{
set until time "to_date('Aug 16 2014 10:30:00','Mon DD YYYY HH24:MI:SS')";
restore database;
recover database;
sql 'alter database open resetlogs';
}
NOTE: The above scripts may be altered to allocate more channels. Good practice would be to review the backup log and use the same number of channels for restore as that used by the backup.?
**)
