Rman recovery scheme and oracle recovery from different machines

Note:
① The premise for recovery is that the backup has been completed
② A database fault occurs when the database is fully recovered, and no committed data is lost during recovery.
Incomplete restoration of the database is a fault in the database. Partial data is lost during restoration.
③ In linux, you need to set the environment variable, that is, the Instance name of the oracle database to be restored: export ORACLE_SID = orcl
④ When Using resetlogs to start the database, a full backup should be performed for the database.

I. Recovery Plan
1. Data Files are lost for full recovery
Copy codeThe Code is as follows:
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAn> SQL 'alter database open ';

2. Lost redo log files and Incomplete recovery
Copy codeThe Code is as follows:
SQL> startup mount;
SQL> recover database until cancel;
SQL> alter database open resetlogs;

3. Incomplete recovery of lost data files, control files, and redo log files
Copy codeThe Code is as follows:
RMAN> startup nomount;
RMAN> restore controfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
SQL> recover database using backup controlfile until cancel;
SQL> alter database open resetlogs;

4. Loss of initialization files, control file data files, and redo log files for Incomplete recovery
Copy codeThe Code is as follows:
SQL> startup nomount pfile = 'd: \ oracle \ product \ 10.2.0 \ db_1 \ admin \ orcl \ pfile \ init. ora.27201414210 '; # pfile path
RMAN> restore spfile from autobackup;
SQL> shutdown immediate;
SQL> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
SQL> recover database using backup controlfile until cancel;
SQL> alter database open resetlogs;

5. Recovery based on time points (commonly used)
Copy codeThe Code is as follows:
RMAN> startup mount;
RMAN> restore database;
RMAN> SQL 'alter session set nls_date_format = "yyyymmdd hh24: mi: ss" '; # set the display time format
RMAN> recover database until time '2014 17:07:00 '; # restore the database to the time point 20140312 17:07:00
RMAN> SQL 'alter database open resetlogs ';

6. SCN-based recovery
Copy codeThe Code is as follows:
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database until scn 963915;
RMAN> SQL 'alter database open resetlogs ';

① Query the current SCN Number:
Copy codeThe Code is as follows:
SQL> select dbms_flashback.get_system_change_number from dual;

② Query time by SCN Number:
Copy codeThe Code is as follows:
SQL> select to_char (scn_to_timestamp (963959), 'yyyymmdd hh24: mi: si') from dual;

③ Query the SCN number by Time:
Copy codeThe Code is as follows:
SQL> select timestamp_to_scn (to_date ('1970 17:55:10 ', 'yyyymmdd hh24: mi: ss') from dual;

④ Query the SCN number for a period of time:
Copy codeThe Code is as follows:
SQL> select * from (select time_dp, scn from smon_scn_time order by time_dp desc) where rownum <20;

7. in-depth understanding of incarnation: data can be restored across resetlogs.
① Each resetlogs will enable incarnation + 1; if you want to restore to the scn of incarnation, You need to first recover to the previous incarnation;

② Incarnation 11 and 12 both include the spot of scn 900000, so when incarnation 12 is used for until scn, It is restored to the scn of the current incarcation; but according to our needs, we need scn 900000 of incarnation 11; so we need to switch incarnation to incarnation 11 to retrieve the data we need.
RMAN> reset database to incarnation 11;

8. Differences between the four statements of recover database
① SQL> recover database using backup controlfile
If the current control file is lost, it is used to tell oracle not to use the scn in controlfile as the recovery end point when the control file of the cold backup is recovered.
② SQL> recover database until cancel
If current/active redo is lost, manually specify the endpoint.
③ SQL> recover database using backup controlfile until cancel;
If the current controlfile is lost and both current and active redo are lost, the archived logs are automatically applied to achieve maximum recovery;
④ SQL> recover database until cancel using backup controlfile;
If the current controlfile is lost and both current and active redo are lost, use the scn in the old redo as the recovery destination. Because no archived logs are applied, all data is lost.

To understand the recover database using backup controlfile, first understand the recover database. When the common recover database or recover tablespace and recover datafile are used, Oracle will take the SCN recorded in the current controlfile as the standard, use the redo entry of archive log and redo log to restore the relevant datafile block to the "SCN recorded by the current controlfile". In some cases, oracle needs to restore the data to a back location than the SCN recorded in the current controlfile (for example, the control file is backup controlfile, or the controlfile is created based on trace .), At this time, you need to use using backup controlfile. recovery will not be limited by the "SCN recorded in the current controlfile. At this time, the limit comes from your statement (until time, until scn), or the available archive log (until cancel ). If the control file is lost, the using backup controlfile option must be used after the control file of the restore backup. Until cancel is not completely restored, that is, the current/active redo is lost, or an archive file is missing after the restore database, it is terminated.
Conclusion:
If you have an old database backup, use recover database or recover database using backup controlfile when all the archived logs and online logs are properly saved, no data is lost.
If the archived or online logs (active or current) are lost after the backup data file restore exists, use recover database using backup controlfile until cancel to achieve maximum recovery.

Ii. Recovery of RMAN from different machines(The instance names of the source and target databases must be the same)

1. Back up the source database
Copy codeThe Code is as follows:
RMAN> backup database plus archivelog;
RMAN> backup current controlfile;

2. Generate the parameter file of the source database, modify the file, and pass it to the target database.
3. Restore on windows
Copy codeThe Code is as follows:
C: \> oradim-new-sid orcl # create an orcl Service
C: \> set ORACLE_SID = orcl
C: \> sqlplus "/as sysdba ";
SQL> startup nomount pfile = 'C: \ INITorcl. ORA ';
SQL> create spfile from pfile = 'C: \ INITorcl. ORA ';
SQL> shutdown immediate;
SQL> exit;
C: \> set ORACLE_SID = orcl
C: \> rman target/
RMAN> startup nomount;
RMAN> restore controlfile to 'location and name of the target Database Control File 'from 'location of the backup control file ';
RMAN> alter database mount;
RMAN> catalog start with 'backup set location '; # Add a backup set to the Control File
RMAN> crosscheck backup;
RMAN> report schema;

Write the following script and run it.
Copy codeThe Code is as follows:
Run
{
Set newname for datafile 1
To 'C: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ SYSTEM. dbf'; # Place SYSTEM. DBF in the instance
Set newname for datafile 2
To 'C: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ undotbs1.dbf ';
Set newname for datafile 3
To 'C: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ SYSAUX. dbf ';
Set newname for datafile 4
To 'C: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ USERS. dbf ';
Set newname for datafile 5
To 'C: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ bank_data02.dbf ';
Restore database;
Switch datafile all;
}
RMAN> recover database;
RMAN> SQL 'alter database open resetlogs ';

Recovery successful