About Oracle RMAN Automatic recovery

In daily work, Oracle Database recovery is sometimes required, such as setting up a test environment, finding historical data, and recovering tests. It is hereby documented that the recovery scripts can be executed in such a way as to schedule tasks or nohup to improve the efficiency of the entire operation.

The test environment used here is as follows:

OS Platform	Red Hat Enterprise Linux Server Release 5.4 (tikanga)-64bit
Database	Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit

1. Install Oracle software on the machine that needs to recover the database (Testsvr01 in this article)

2. Transfer the Rman backup file of the Oracle database and the corresponding archive log to the target machine (TESTSVR01),

And make sure that Oracle users have access to the chown-r oracle:oinstall/backup_dir command

Change the owner of the backup file

3. Edit the parameter file Pfile in the $oracle_home/dbs directory to facilitate the launch of the ORACLE instance, the pfile of this article is Initmydb.ora

The pfile can be created in the source library via the Create Pfile command and then uploaded to the target machine to modify the corresponding parameter values

In Pfile, note the values of the following parameters

*.control_files= '/data1/oradata/mydb/control01.ctl ', '/data1/oradata/mydb/control02.ctl ', '/data1/oradata/mydb/ Control03.ctl ' *.log_archive_dest_1= ' Location=/data3/rman_bak/mydb/arc ' *.log_archive_format= ' arc%s_%t_%r.dbf ' *.db_name= ' MyDB ' *.undo_tablespace= ' UNDOTBS1 ' *.undo_management=auto *.job_queue_processes=0

4. ensure that the directory used in the Pfile is already present and that it is a master Oracle

5. Create the auto_recovery.sh script in the/home/oracle directory and give the executable permission

auto_recovery.sh Content Example:

[Email protected] Testsvr01 oracle]# cat auto_recovery.sh #!/bin/bash Source/home/oracle/.bash_profile shell_name=$ (basename) If [$#-ne 1]; Then Echo-e "\ n Usage: ${shell_name} oracle_sid \ n" Exit Fi Oracle_sid=$1 Script_path=/home/oracle Rman_sql=${script_path}/${oracle_sid}_rman.sql Rman_log=${script_path}/${oracle_sid}_rman.log Sqlplus_sql=${script_path}/${oracle_sid}_sqlplus.sql Sqlplus_log=${script_path}/${oracle_sid}_sqlplus.log Control_sql=${script_path}/${oracle_sid}_control.sql If [!-S ${rman_sql}]; Then echo "${rman_sql} doesn ' t exist!" Exit elif [!-S ${sqlplus_sql}]; Then echo "${sqlplus_sql} doesn ' t exist!" Exit elif [!-S ${control_sql}]; Then echo "${control_sql} doesn ' t exist!" Exit Else Ls-lrth ${script_path}/${oracle_sid}*.sql Fi Su-oracle <<eof Source/home/oracle/.bash_profile Export oracle_sid=$1 Export Oracle_home=/u01/app/oracle/11.2.0/db_1 Export path= $PATH: $ORACLE _home/bin Echo-e "RMAN part Begin ... \ n" Echo ' date + '%y%m%d%h:%m:%s ' Rman target/log= ' ${rman_log} ' Cmdfile=${rman_sql} Echo ' date + '%y%m%d%h:%m:%s ' Echo-e "RMAN Parat End. \ n " Echo-e "SQLPLUS part Begin ... \ n" Echo ' date + '%y%m%d%h:%m:%s ' Sqlplus-s/As Sysdba @${sqlplus_sql} $ORACLE _sid > ${sqlplus_log} Echo ' date + '%y%m%d%h:%m:%s ' Echo-e "SQLPLUS part End. \ n " Eof

6. in the/home/oracle directory, edit the Rman script ${oracle_sid}_rman.sql, this is Mydb_rman.sql

mydb_rman.sql Content Example:

run{ Startup Nomount; Restore Controlfile from '/data3/rman_bak/mydb/datafile/mydb_20170823_46007_1_control '; ALTER DATABASE mount; Crosscheck backup; Delete noprompt expired backup; Catalog start with '/data3/rman_bak/mydb/' noprompt; Allocate channel C1 device type disk; Allocate channel C2 device type disk; Set newname for datafile 1  to   '/data1/oradata/mydb/datafile/system.dbf '; Set newname for datafile 2  to   '/data1/oradata/mydb/datafile/sysaux.dbf '; Set newname for datafile 3  to   '/data1/oradata/mydb/datafile/undotbs1.dbf '; Set newname for datafile 4  to   '/data1/oradata/mydb/datafile/users.dbf '; Restore datafile 1; Restore DataFile 2; Restore DataFile 3; Restore DataFile 4; Switch datafile all; Release channel C1; Release channel C2; Shutdown immediate; }

7. Edit the ${oracle_sid}_sqlplus.sql script in the/home/oracle directory, this is Mydb_sqlplus.sql

mydb_sqlplus.sql Content Example:

[Email protected] Testsvr01 oracle]# cat Mydb_sqlplus.sql Startup Nomount; @/home/oracle/&1._control.sql Set AutoRecovery on; Recover database using Backup controlfile until cancel; Set AutoRecovery off; ALTER DATABASE open resetlogs; Alter tablespace TEMP add tempfile '/data1/oradata/mydb/datafile/temp01.dbf ' size 1G autoextend off; Shutdown immediate; Startup Alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss '; Select Instance_name,status,startup_time from V$instance; Select Name,open_mode,log_mode,sysdate from V$database; Shutdown immediate; Exit

8. in the/home/oracle directory, edit the script used to create the control file ${oracle_sid}_control.sql, this article is Mydb_control.sql

mydb_control.sql Content Example:

[email protected] oracle]# cat Mydb_control.sql CREATE controlfile Reuse DATABASE "MYDB" Resetlogs Noarchivelog Maxlogfiles 20 Maxlogmembers 5 Maxdatafiles 1000 Maxinstances 8 Maxloghistory 4927 LOGFILE GROUP 1 ( '/data1/oradata/mydb/onlinelog/redo1.log ' ) SIZE 400M BLOCKSIZE 512, GROUP 2 ( '/data1/oradata/mydb/onlinelog/redo2.log ' ) SIZE 400M BLOCKSIZE 512, GROUP 3 ( '/data1/oradata/mydb/onlinelog/redo3.log ' ) SIZE 400M BLOCKSIZE 512 --STANDBY LOGFILE DataFile '/data1/oradata/mydb/datafile/system.dbf ', '/data1/oradata/mydb/datafile/sysaux.dbf ', '/data1/oradata/mydb/datafile/users.dbf ', '/DATA1/ORADATA/MYDB/DATAFILE/UNDOTBS1.DBF ' CHARACTER SET we8mswin1252 ;

9. Execute auto_recovery.sh script in the background via crontab or nohup

Nohup/home/oracle/auto_recovery.sh MyDB &

Note:

This article only recovers some of the data files, so you need to rebuild the control file, if it is a full-library recovery, you can not rebuild the control file, the script of this article to make the appropriate modifications??? Can.

Points to Comb

First, how to automatically apply the archive log when recover

According to the official Oracle documentation, there are two ways to implement automatic recover using Sqlplus:

1. Use the set AutoRecovery on command, which is the method used in this article

2. Use the automatic option in the Recover command

STARTUP MOUNT RECOVER AUTOMATIC DATABASE ALTER DATABASE OPEN;

Second, the path of the archive log

In general, the archive logs required for recover are present in log_archive_dest_n and log_archive_format by default The path and file name defined by these two initialization parameters. Oracle can apply them automatically if the corresponding archive logs exist.

If the target library is not the same as the path in the source library, you can modify the values of the two parameters, Log_archive_dest_n and Log_archive_format, in the following ways

1. Modify the value of the corresponding parameter in the Pfile

2. Using the alter system command to modify

alter system set Log_archive_dest_1 = ' location =/oracle/oradata/trgt/arch/'; alter system set Log_archive_format = ' Arcr_%t_%s.arc ';

If you do not want to modify the values of the above two parameters, you can also specify the path of the archive log required by recover in the following ways

1. Set logsource parameters

Set Logsource '/tmp ' RECOVER AUTOMATIC tablespace Users

2. Using the FROM option in the Recover command

RECOVER AUTOMATIC tablespace users from "/tmp"

--end.--

View Original: http://www.dosin.cn/products/dbwk/

About Oracle RMAN Automatic recovery