Shulou(Shulou.com)06/01 Report--

In daily work, Oracle database recovery is sometimes needed, such as setting up test environment, finding historical data, recovery testing and so on.

It is recorded that recovery scripts can be executed through scheduled tasks or nohup to improve the efficiency of the whole operation.

The test environment used here is as follows:

OS PlatformRed Hat Enterprise Linux Server release 5.4 (Tikanga)-64bitDatabaseOracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit

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

two。 Transfer the RMAN backup files and corresponding archive logs of the Oracle database to the target machine (testsvr01)

And make sure that the oracle user has permission to access it through 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 start the Oracle instance. The pfile of this article is initmydb.ora.

You can use the create pfile command to create a pfile in the source library, and then pass it 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. Make sure that the directory used in pfile already exists and that the owner is oracle

5. Create an auto_recovery.sh script in the / home/oracle directory and grant executable permissions

Example of auto_recovery.sh content:

[root@ testsvr01 oracle] # cat autorecovery. Ne. If bind ne source / home/oracle/.bash_profile SHELL_NAME=$ (ne $0) source [$#-ne 1] Then echo-e "\ n Usage: ${SHELL_NAME} ORACLE_SID\ n" exitfi ORACLE_SID=$1SCRIPT_PATH=/home/oracleRMAN_SQL=$ {SCRIPT_PATH} / ${ORACLE_SID} _ rman.sqlRMAN_LOG=$ {SCRIPT_PATH} / ${ORACLE_SID} _ rman.logSQLPLUS_SQL=$ {SCRIPT_PATH} / ${ORACLE_SID} _ sqlplus.sqlSQLPLUS_LOG=$ {SCRIPT_PATH} / ${ORACLE_SID} _ sqlplus.logCONTROL_SQL=$ {SCRIPT_PATH} / ${ORACLE_SID} _ Control.sql if [!-s ${RMAN_SQL}] Then echo "${RMAN_SQL} doesn't exist!" Exitelif [!-s ${SQLPLUS_SQL}]; then echo "${SQLPLUS_SQL} doesn't exist!" Exitelif [!-s ${CONTROL_SQL}]; then echo "${CONTROL_SQL} doesn't exist!" Exitelse ls-lrth ${SCRIPT_PATH} / ${ORACLE_SID} * .sqlfi su-oracle% SQLPLUS_LOG%date / ttime / texit

6. Edit the RMAN script ${ORACLE_SID} _ rman.sql under the / home/oracle directory, this article is mydb_rman.sql

Example of mydb_rman.sql content:

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 *

7. Edit the ${ORACLE_SID} _ sqlplus.sql script under the / home/oracle directory, and this article is mydb_sqlplus.sql

Example of mydb_sqlplus.sql content:

[root@ testsvr01 oracle] # cat mydb_sqlplus.sqlstartup nomount;@/home/oracle/&1._control.sqlset 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 hh34:mi:ss';select instance_name,status,startup_time from vastly instantly select name,open_mode,log_mode,sysdate from vested databaseshutdown immediate;exit

8. Edit the script ${ORACLE_SID} _ control.sql used to create the control file under the / home/oracle directory, this article is mydb_control.sql

Example of mydb_control.sql content:

[root@testsvr01 oracle] # cat mydb_control.sqlCREATE CONTROLFILE REUSE DATABASE "MYDB" RESETLOGS noARCHIVELOG MAXLOGFILES 20 MAXLOGMEMBERS 5 MAXDATAFILES 1000 MAXINSTANCES 8 MAXLOGHISTORY 4927LOGFILE 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 LOGFILEDATAFILE'/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 scripts in the background through crontab or nohup

Nohup / home/oracle/auto_recovery.sh mydb &

Note:

In this paper, only part of the data files are restored, so the control files need to be rebuilt. If the whole library is restored, the script can be modified appropriately without rebuilding the control files.

Sort out the main points

1. How to automatically apply archived logs in recover

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

1. Use the set autorecovery on command, the method used in this article

two。 Use the automatic option in the recover command

STARTUP MOUNT

RECOVER AUTOMATIC DATABASE

ALTER DATABASE OPEN

2. The path to the archived log

Generally speaking, the archive log that needs to be applied in recover exists by default in the path and file name defined by the initialization parameters LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_FORMAT. If the corresponding archive logs exist, Oracle can apply them automatically.

If the path in the target library is different from that in the source library, you can modify the values of the parameters LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_FORMAT in the following ways

1. Modify the value of the corresponding parameter in pfile

two。 Use 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 that recover needs to use in the following ways

1. Set LOGSOURCE parameters

Set logsource'/ tmp'

RECOVER AUTOMATIC TABLESPACE user

two。 Use the FROM option in the recover command

RECOVER AUTOMATIC TABLESPACE users FROM "/ tmp

-- End.--

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.