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

This article is to share with you the content of a sample analysis of Oracle RMAN heterogeneous recovery. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Oracle RMAN off-machine recovery

Experimental scenario:

The local disk of the database server is damaged and the database cannot be recovered locally.

Complete RMAN complete and archived backup exists, and off-machine recovery is carried out through RMAN.

Experimental environment:

Source library: IP (192.0.2.12), HOSTNAME (edbj2p2), DB (PROD3)

Target library: IP (192.0.2.11), HOSTNAME (edbj2p1)

The experimental process is as follows:

One: source database, create test data:

SQL > create user chen identified by a

SQL > grant connect,resource to chen

SQL > conn chen/a

SQL > create table test as select level as id from dual connect by level select * from test

ID

-

one

two

three

four

five

Second, source database, complete RMAN

[oracle@edbjr2p2 bin] $rman target /

Recovery Manager: Release 11.2.0.3.0-Production on Wed Jul 19 21:48:16 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to target database: PROD3 (DBID=1562953461)

RMAN > show all

Using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name PROD3 are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'% favored; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE' DEFAULT' OPTIMIZE FOR LOAD TRUE; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO'/ u01qapplash oracleandproduct11.2.0Uniplicationdbroom1anddbsAccordsSnapcfPROD3.fCom; # default

RMAN > run {

2 > allocate channel C1 type disk

3 > backup full database format'/ home/oracle/rmanbak/db_full_%T_%u.bak' tag='FULL' include current controlfile

4 > sql 'alter system archive log current'

5 > backup archivelog all format'/ home/oracle/rmanbak/arc_%T_%u.bak' delete all input

6 > release channel C1

7 >}

Allocated channel: c1

Channel c1: SID=139 device type=DISK

Starting backup at 19-JUL-17

Channel c1: starting full datafile backup set

Channel C1: specifying datafile (s) in backup set

Input datafile file number=00004 name=/u01/app/oracle/oradata/PROD3/users01.dbf

Input datafile file number=00001 name=/u01/app/oracle/oradata/PROD3/system01.dbf

Input datafile file number=00002 name=/u01/app/oracle/oradata/PROD3/sysaux01.dbf

Input datafile file number=00003 name=/u01/app/oracle/oradata/PROD3/undotbs01.dbf

Channel c1: starting piece 1 at 19-JUL-17

Channel c1: finished piece 1 at 19-JUL-17

Piece handle=/home/oracle/rmanbak/db_full_20170719_01s9p6s1.bak tag=FULL comment=NONE

Channel c1: backup set complete, elapsed time: 00:00:35

Channel c1: starting full datafile backup set

Channel C1: specifying datafile (s) in backup set

Including current control file in backup set

Including current SPFILE in backup set

Channel c1: starting piece 1 at 19-JUL-17

Channel c1: finished piece 1 at 19-JUL-17

Piece handle=/home/oracle/rmanbak/db_full_20170719_02s9p6t4.bak tag=FULL comment=NONE

Channel c1: backup set complete, elapsed time: 00:00:03

Finished backup at 19-JUL-17

Sql statement: alter system archive log current

Starting backup at 19-JUL-17

Current log archived

Channel c1: starting archived log backup set

Channel C1: specifying archived log (s) in backup set

Input archived log thread=1 sequence=7 RECID=1 STAMP=949787112

Input archived log thread=1 sequence=8 RECID=2 STAMP=949787564

Input archived log thread=1 sequence=9 RECID=3 STAMP=949787564

Channel c1: starting piece 1 at 19-JUL-17

Channel c1: finished piece 1 at 19-JUL-17

Piece handle=/home/oracle/rmanbak/arc_20170719_03s9p6tc.bak tag=TAG20170719T215244 comment=NONE

Channel c1: backup set complete, elapsed time: 00:00:03

Channel C1: deleting archived log (s)

Archived log file name=/u01/app/oracle/fast_recovery_area/PROD3/archivelog/2017_07_19/o1_mf_1_7_dpyrm5bd_.arc RECID=1 STAMP=949787112

Archived log file name=/u01/app/oracle/fast_recovery_area/PROD3/archivelog/2017_07_19/o1_mf_1_8_dpys1d88_.arc RECID=2 STAMP=949787564

Archived log file name=/u01/app/oracle/fast_recovery_area/PROD3/archivelog/2017_07_19/o1_mf_1_9_dpys1dd4_.arc RECID=3 STAMP=949787564

Finished backup at 19-JUL-17

Released channel: c1

Three: pack the backup file and transfer it to the target database

Source database:

[oracle@edbjr2p2 rmanbak] $pwd

/ home/oracle/rmanbak

[oracle@edbjr2p2 rmanbak] $tar-zcvf rmanbak.tar.gz *

Arc_20170719_03s9p6tc.bak

Db_full_20170719_01s9p6s1.bak

Db_full_20170719_02s9p6t4.bak

[oracle@edbjr2p2 rmanbak] $ll-rth

Total 361M

-rw-r- 1 oracle oinstall 239m Jul 19 21:52 db_full_20170719_01s9p6s1.bak

-rw-r- 1 oracle oinstall 9.2m Jul 19 21:52 db_full_20170719_02s9p6t4.bak

-rw-r- 1 oracle oinstall 51m Jul 19 21:52 arc_20170719_03s9p6tc.bak

-rw-r--r-- 1 oracle oinstall 61m Jul 19 21:56 rmanbak.tar.gz

[oracle@edbjr2p2 rmanbak] $scp rmanbak.tar.gz 192.0.2.11:/home/oracle/rmanbak

The authenticity of host '192.0.2.11 (192.0.2.11)' can't be established.

RSA key fingerprint is 4a:08:1a:c4:c8:bb:3b:01:49:b5:2f:58:af:9e:06:af.

Are you sure you want to continue connecting (yes/no)? Yes

Warning: Permanently added '192.0.2.11' (RSA) to the list of known hosts.

Oracle@192.0.2.11's password:

Rmanbak.tar.gz 100% 61MB 30.3MB/s 00:02

Target Library:

[oracle@edbjr2p1 rmanbak] $pwd

/ home/oracle/rmanbak

[oracle@edbjr2p1 rmanbak] $tar-zxvf rmanbak.tar.gz

Arc_20170719_03s9p6tc.bak

Db_full_20170719_01s9p6s1.bak

Db_full_20170719_02s9p6t4.bak

[oracle@edbjr2p1 rmanbak] $ll-rth

Total 361M

-rw-r- 1 oracle oinstall 239m Jul 19 21:52 db_full_20170719_01s9p6s1.bak

-rw-r- 1 oracle oinstall 9.2m Jul 19 21:52 db_full_20170719_02s9p6t4.bak

-rw-r- 1 oracle oinstall 51m Jul 19 21:52 arc_20170719_03s9p6tc.bak

-rw-r--r-- 1 oracle oinstall 61m Jul 19 21:58 rmanbak.tar.gz

Three: target library, create the corresponding directory

[oracle@edbjr2p1 rmanbak] $mkdir-p / u01/app/oracle/oradata/PROD3

[oracle@edbjr2p1] $mkdir-p / u01/app/oracle/admin/PROD3/adump

[oracle@edbjr2p1 rmanbak] $mkdir-p / u01/app/oracle/fast_recovery_area/PROD3/archivelog/2017_07_19

Four: target library, run RMAN to perform data recovery

(1) restore parameter files

(2) restore control files

(3) restore the database

[oracle@edbjr2p1 rmanbak] $export ORACLE_SID=PROD3

[oracle@edbjr2p1 rmanbak] $rman target /

Recovery Manager: Release 11.2.0.3.0-Production on Wed Jul 19 22:05:46 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to target database (not started)

RMAN > set dbid 1562953461

Executing command: SET DBID

RMAN > startup nomount

Startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file'/ u01ActionApplicationoracleUniplicationproductUniverse 11.2.0 Universe dbhomeowners 1anddbsAccording to initPROD3.ora'

Starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area 159019008 bytes

Fixed Size 1343612 bytes

Variable Size 79695748 bytes

Database Buffers 71303168 bytes

Redo Buffers 6676480 bytes

RMAN > restore spfile from'/ home/oracle/rmanbak/db_full_20170719_02s9p6t4.bak'

Starting restore at 19-JUL-17

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=111 device type=DISK

Channel ORA_DISK_1: restoring spfile from AUTOBACKUP / home/oracle/rmanbak/db_full_20170719_02s9p6t4.bak

Channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 19-JUL-17

RMAN > startup nomount force

Oracle instance started

Total System Global Area 209235968 bytes

Fixed Size 1343948 bytes

Variable Size 180358708 bytes

Database Buffers 20971520 bytes

Redo Buffers 6561792 bytes

RMAN > restore controlfile from'/ home/oracle/rmanbak/db_full_20170719_02s9p6t4.bak'

Starting restore at 19-JUL-17

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=134 device type=DISK

Channel ORA_DISK_1: restoring control file

Channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Output file name=/u01/app/oracle/oradata/PROD3/control01.ctl

Output file name=/u01/app/oracle/oradata/PROD3/control02.ctl

Finished restore at 19-JUL-17

RMAN > alter database mount

Database mounted

Released channel: ORA_DISK_1

RMAN > restore database

Starting restore at 19-JUL-17

Starting implicit crosscheck backup at 19-JUL-17

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=134 device type=DISK

Crosschecked 1 objects

Finished implicit crosscheck backup at 19-JUL-17

Starting implicit crosscheck copy at 19-JUL-17

Using channel ORA_DISK_1

Finished implicit crosscheck copy at 19-JUL-17

Searching for all files in the recovery area

Cataloging files...

No files cataloged

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting datafile backup set restore

Channel ORA_DISK_1: specifying datafile (s) to restore from backup set

Channel ORA_DISK_1: restoring datafile 00001 to / u01/app/oracle/oradata/PROD3/system01.dbf

Channel ORA_DISK_1: restoring datafile 00002 to / u01/app/oracle/oradata/PROD3/sysaux01.dbf

Channel ORA_DISK_1: restoring datafile 00003 to / u01/app/oracle/oradata/PROD3/undotbs01.dbf

Channel ORA_DISK_1: restoring datafile 00004 to / u01/app/oracle/oradata/PROD3/users01.dbf

Channel ORA_DISK_1: reading from backup piece / home/oracle/rmanbak/db_full_20170719_01s9p6s1.bak

Channel ORA_DISK_1: piece handle=/home/oracle/rmanbak/db_full_20170719_01s9p6s1.bak tag=FULL

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:02:39

Finished restore at 19-JUL-17

RMAN > recover database

Starting recover at 19-JUL-17

Using channel ORA_DISK_1

Starting media recovery

Unable to find archived log

Archived log thread=1 sequence=8

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: failure of recover command at 07/19/2017 22:20:27

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 8 and starting SCN of 241384

-reason for error: RMAN backup does not back up the current redo logfile file, and redo logfile cannot be found during off-machine recovery, so error rman-06054 is reported.

-solution: incomplete recovery based on SCN

RMAN > run {

2 > set until scn 241384

3 > recover database

4 >}

Executing command: SET until clause

Starting recover at 19-JUL-17

Using channel ORA_DISK_1

Starting media recovery

Media recovery complete, elapsed time: 00:00:00

Finished recover at 19-JUL-17

RMAN > alter database open resetlogs

Database opened

Five: target library, verify data

[oracle@edbjr2p1 rmanbak] $export ORACLE_SID=PROD3

[oracle@edbjr2p1 rmanbak] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 19 22:41:57 2017

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL > select * from chen.test

ID

-

one

two

three

four

five

Thank you for reading! This is the end of this article on "sample Analysis of Oracle RMAN heterogeneous recovery". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.