Shulou(Shulou.com)05/31 Report--

What is the principle of hot backup recovery and off-machine recovery in Oracle backup recovery? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Hot backup recovery and off-machine recovery of Oracle backup and recovery

Principle:

The database must be running in archive mode, otherwise the backup is meaningless. Freeze the block before backup so that the scn number does not change, then cp the physical file, and finally thaw the block. The dml statement of this process can be executed normally, and the action is written in the log file. When the scn number is unfrozen, the contents of the log file will be automatically written to the data file.

Process:

1. Full database backup:

1) alter database begin backup

2) cp physical file

3) alter database end backup

Script:

Spool / u01/oracle/jiaoben/bf2.sql

Select'ho cp'| | name |'/ u01OnOnOnOnCure rebeiUniplex 'from v$datafile

Spool off

Alter database backup controlfile to'/ u01qoracleandrebeiUniUniple control.ctl'

Create pfile='/u01/oracle/rebei/initorcl.ora' from spfile

Alter database begin backup

@ / u01/oracle/jiaoben/bf2.sql

Alter database end backup

2. Table space level backup:

1) alter tablespace begin backup

2) data files under cp tablespace

3) alter tablespace end backup

Script:

Spool / u01/oracle/home/thot.sql

Select 'alter tablespace' | | tablespace_name | | 'begin backup;' | | chr (10) | |' ho cp'| | file_name | |'/ u01swap oracleHomeCome'| | chr (10) | | 'alter tablespace' | | tablespace_name | 'end backup;' | | chr (10) from dba_data_files |

Spool off

Start / u01/oracle/home/thot.sql

Ho rm / u01/oracle/home/thot.sql

Alter database backup controlfile to'/ u01qoraclecontrol. CTL'

Create pfile='/u01/oracle/home/initorcl.ora' from spfile

Recovery: the data files that can be offline are recovered offline, and cannot be started offline to the mount stage.

1. Restore method for loss of users tablespace:

1) take the data file offline

2) physical cp backup files

3) recover database

4) bring data files online

2. Restore method for loss of system tablespace:

1) start the database to mount status

2) physical cp backup files

3) recover database

4) alter database open

3. All data files are missing:

1) shutdown abort

2) startup mount

3) cp all backup physical files to the data file directory

4) recover database

5) alter database open

4. Log files are missing

1) shutdown immediate

2) startup (auto hang up)

3) recover database until cancel

4) alter database open resetlogs

5. Control file loss recovery

1) shutdown abort

2) startup (auto hang up)

3) cp the control file back to its original location

4) alter database mount

5) recover database using backup controlfile; > auto

6) recover database using backup controlfile; > enter the online log path and enter respectively

7) alter database open resetlogs

6 、

Control files, log files, data files are missing

1) shutdown abort

2) startup (auto hang up)

3) cp the control file back to its original location

4) alter database mount

5) return all cp data files to their original location

6) recover database using backup controlfile until cancel

7) alter database open resetlogs

Different machine migration recovery + minor version upgrade: (oracle 11g 11.2.0.1~oracle 11g 11.2.0.4)

1. Transfer hot backup files and archive logs to the target machine

2. Startup pfile='/ backs up the pfile file 'mount

3 、 recover database using backup controlfile until cancel

4 、 alter database open upgrade resetlogs

The upgrade method will lose the most recent online log information.

The strange machine made by oneself resumes the actual combat.

LINUX operating system REDHAT6.5

ORACLE is 11.2.0.4

Make a RMAN backup of the source database

Copy backup files to the test library

The operation in the test library:

Remember to first put the backup files in a folder where oracle users have read and write permissions, and then grant oracle users access to all backup files.

Chown oracle.oinstall full_bak*

In fact, all you have to do is change where the user belongs.

Modify parameter file

Vi / opt/pfile.ora

Limsdb.__db_cache_size=4026531840

Limsdb.__java_pool_size=33554432

Limsdb.__large_pool_size=50331648

Limsdb.__oracle_base='/home/db/u01/app/oracle'#ORACLE_BASE set from environment

Limsdb.__pga_aggregate_target=1677721600

Limsdb.__sga_target=5033164800

Limsdb.__shared_io_pool_size=0

Limsdb.__shared_pool_size=872415232

Limsdb.__streams_pool_size=16777216

* .audit_file_dest='/home/db/u01/app/oracle/admin/limstest/adump'

* .audit_trail='db'

* .compatible='11.2.0.4.0'

* .control_files='/home/db/u01/app/oracle/oradata/limstest/control01.ctl','/home/db/u01/app/oracle/fast_recovery_area/limstest/control02.ctl'

* .db_block_size=8192

* .db_domain=''

* .db_name='limsdb'

* .db_recovery_file_dest='/home/db/u01/app/oracle/fast_recovery_area'

* .db_recovery_file_dest_size=4385144832

* .diagnostic_dest='/home/db/u01/app/oracle'

* .dispatchers=' (PROTOCOL=TCP) (SERVICE=limsdbXDB)'

* .open_cursors=300

* .pga_aggregate_target=1672478720

* .processes=150

* .remote_login_passwordfile='EXCLUSIVE'

* .sga_target=5017436160

* .undo_tablespace='UNDOTBS1'

Export ORACLE_SID=limsdb

Rman target /

Startup nomount pfile='/opt/pfile20190926.ora'

Restore controlfile from'/ opt/full_cont_LIMSDBxx_876748607_20190925_7356_1'

Mount database

Catalog start with'/ opt/bak/'

Query the data file path select file#,name from v$datafile under the source database

Run {

Set newname for datafile'+ DATA/limsdb/datafile/system.256.942403651' to'/ home/db/u01/app/oracle/oradata/limstest/system01.dbf'

Set newname for datafile'+ DATA/limsdb/datafile/sysaux.257.942403655' to'/ home/db/u01/app/oracle/oradata/limstest/sysaux01.dbf'

Set newname for datafile'+ DATA/limsdb/datafile/undotbs1.258.942403659' to'/ home/db/u01/app/oracle/oradata/limstest/undotbs1.dbf'

Set newname for datafile'+ DATA/limsdb/datafile/users.259.942403659' to'/ home/db/u01/app/oracle/oradata/limstest/users.dbf'

Set newname for datafile'+ DATA/limsdb/datafile/undotbs2.271.942403927' to'/ home/db/u01/app/oracle/oradata/limstest/undotbs2.dbf'

Set newname for datafile'+ DATA/limsdb/datafile/byhealth_data.281.943025409' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata01.dbf'

Set newname for datafile'+ DATA/limsdb/datafile/byhealth_data.282.943025591' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata02.dbf'

Set newname for datafile'+ DATA/limsdb/datafile/byhealth_data.283.943025709' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata03.dbf'

Set newname for datafile'+ DATA/limsdb/datafile/byhealth_data.284.943025841' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata04.dbf'

Set newname for datafile'+ DATA/limsdb/datafile/byhealth_data.285.943025965' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata05.dbf'

Set newname for datafile'+ DATA/limsdb/datafile/byhealth_data.286.943026085' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata06.dbf'

Set newname for datafile'+ DATA/limsdb/datafile/byhealth_data.287.943026201' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata07.dbf'

Set newname for datafile'+ DATA/limsdb/datafile/byhealth_data.288.943026369' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata08.dbf'

Set newname for datafile'+ DATA/limsdb/datafile/byhealth_data.289.943026563' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata09.dbf'

Set newname for datafile'+ DATA/limsdb/datafile/byhealth_data.290.943026913' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata10.dbf'

Restore database

}

Export ORACLE_SID=limsdb

Sqlplus / as sysdba

Alter database open

Alter database open resetlogs

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 1-see DBWR trace file

ORA-01110: datafile 1:'+ DATA/limsdb/datafile/system.256.942403651'

Alter database rename file'+ DATA/limsdb/datafile/system.256.942403651' to'/ home/db/u01/app/oracle/oradata/limstest/system01.dbf'

Alter database rename file'+ DATA/limsdb/datafile/sysaux.257.942403655' to'/ home/db/u01/app/oracle/oradata/limstest/sysaux01.dbf'

Alter database rename file'+ DATA/limsdb/datafile/undotbs1.258.942403659' to'/ home/db/u01/app/oracle/oradata/limstest/undotbs1.dbf'

Alter database rename file'+ DATA/limsdb/datafile/users.259.942403659' to'/ home/db/u01/app/oracle/oradata/limstest/users.dbf'

Alter database rename file'+ DATA/limsdb/datafile/undotbs2.271.942403927' to'/ home/db/u01/app/oracle/oradata/limstest/undotbs2.dbf'

Alter database rename file'+ DATA/limsdb/datafile/byhealth_data.281.943025409' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata01.dbf'

Alter database rename file'+ DATA/limsdb/datafile/byhealth_data.282.943025591' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata02.dbf'

Alter database rename file'+ DATA/limsdb/datafile/byhealth_data.283.943025709' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata03.dbf'

Alter database rename file'+ DATA/limsdb/datafile/byhealth_data.284.943025841' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata04.dbf'

Alter database rename file'+ DATA/limsdb/datafile/byhealth_data.285.943025965' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata05.dbf'

Alter database rename file'+ DATA/limsdb/datafile/byhealth_data.286.943026085' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata06.dbf'

Alter database rename file'+ DATA/limsdb/datafile/byhealth_data.287.943026201' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata07.dbf'

Alter database rename file'+ DATA/limsdb/datafile/byhealth_data.288.943026369' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata08.dbf'

Alter database rename file'+ DATA/limsdb/datafile/byhealth_data.289.943026563' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata09.dbf'

Alter database rename file'+ DATA/limsdb/datafile/byhealth_data.290.943026913' to'/ home/db/u01/app/oracle/oradata/limstest/limsdata10.dbf'

Select group#,member from v$logfile order by 1

Alter database clear logfile group 9

Alter database clear logfile group 10

Alter database clear logfile group 11

Alter database clear logfile group 12

Alter database clear logfile group 13

Alter database clear logfile group 14

Alter database clear logfile group 15

Alter database clear logfile group 16

Alter database clear logfile group 17

Alter database clear logfile group 18

Remember to set up spfile before that

Create spfile from pfile='/tmp/initorcl1.ora'

Alter database open resetlogs

Recover database using backup controlfile

Auto

Alter system set "_ allow_resetlogs_corruption" = true scope=spfile

Recover database using backup controlfile until cancel

Cancel

Alter database open resetlogs

Startup force

Alter database open resetlogs

Alter system set "_ allow_resetlogs_corruption" = true scope=spfile

Startup force

This is the answer to the question about the principle of hot backup recovery and off-machine recovery in Oracle backup recovery. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.