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

Editor to share with you how RMAN quickly restore the database, I hope you will learn something after reading this article, let's discuss it together!

10g will use RMAN backup and restore is generally the work of DBA, high technical requirements, and a deeper understanding of the organizational structure of oracle can operate, and because database failures are not easy to occur, most DBA will not remember commands, need to check by hand, the scripts for all kinds of missing files are different, for example

Control file loss recovery instruction: restore controlfile from autobackup

Loss of redolog: alter database clear (unarchived) logfile

Incomplete recovery instruction: recover database until cancel

After 11g, rman has a richer instruction set and repair methods, so that ordinary operators can quickly repair database failures (list, advise, repair).

See the following experiment.

In the first case, the simulation control file is lost and the controlfile is deleted.

one

two

three

four

five

six

seven

eight

nine

SQL > startup

ORACLE instance started.

Total SystemGlobalArea 510554112 bytes

FixedSize 1345968 bytes

VariableSize 171968080 bytes

DatabaseBuffers 331350016 bytes

Redo Buffers 5890048 bytes

ORA-00205: errorinidentifying control file,checkalert logformore info

Starting the database found that the database could not be started. Now let's try to recover it in two ways:

The traditional method:

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

RMAN > restore controlfile from autobackup

Starting restoreat30-AUG-16

Using targetdatabasecontrol fileinsteadofrecovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=20 device type=DISK

Recovery area destination: / u01/app/oracle/fra

Databasename (ordatabaseuniquename) usedforsearch: PROD2

Channel ORA_DISK_1: AUTOBACKUP / u01/app/oracle/fra/PROD2/autobackup/2016_08_24/o1_mf_s_920718874_cvt48tkl_.bkp foundinthe recovery area

AUTOBACKUP searchwithformat "% F" notattempted because DBID wasnotset

Channel ORA_DISK_1: restoring control filefromAUTOBACKUP / u01/app/oracle/fra/PROD2/autobackup/2016_08_24/o1_mf_s_920718874_cvt48tkl_.bkp

Channel ORA_DISK_1: control file restorefromAUTOBACKUP complete

Outputfilename=/u01/app/oracle/oradata/PROD2/control01.ctl

Outputfilename=/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl

Finished restoreat30-AUG-16

11g fast recovery method:

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

thirty-four

thirty-five

thirty-six

thirty-seven

thirty-eight

thirty-nine

forty

forty-one

forty-two

forty-three

forty-four

forty-five

forty-six

forty-seven

forty-eight

forty-nine

fifty

fifty-one

fifty-two

fifty-three

fifty-four

fifty-five

fifty-six

fifty-seven

fifty-eight

fifty-nine

sixty

sixty-one

sixty-two

sixty-three

sixty-four

RMAN > list failure

Using targetdatabasecontrol fileinsteadofrecovery catalog

ListofDatabaseFailures

=

Failure ID Priority Status TimeDetected Summary

--

712 CRITICALOPEN 30-AUG-16 Control file / u01/app/oracle/oradata/PROD2/control01.ctlismissing

RMAN > advise failure

ListofDatabaseFailures

=

Failure ID Priority Status TimeDetected Summary

--

712 CRITICALOPEN 30-AUG-16 Control file / u01/app/oracle/oradata/PROD2/control01.ctlismissing

Analyzing automatic repair options; this may takesometime

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=20 device type=DISK

Analyzing automatic repair options complete

Mandatory Manual Actions

=

Nomanual actions available

Optional Manual Actions

=

Nomanual actions available

Automated Repair Options

=

OptionRepair Description

--

1 Use a multiplexed copytorestore control file / u01/app/oracle/oradata/PROD2/control01.ctl

Strategy: The repair includes complete media recoverywithnodata loss

Repair script: / u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_1499999453.hm

RMAN > repair failure

Strategy: The repair includes complete media recoverywithnodata loss

Repair script: / u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_1499999453.hm

Contentsofrepair script:

# restore control file using multiplexed copy

Restore controlfilefrom'/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl'

Sql'alter database mount'

Do you really wanttoexecutethe above repair (enter YESorNO)? Yes

Executing repair script

Starting restoreat30-AUG-16

Using channel ORA_DISK_1

Channel ORA_DISK_1: copied control file copy

Outputfilename=/u01/app/oracle/oradata/PROD2/control01.ctl

Outputfilename=/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl

Finished restoreat30-AUG-16

Sql statement:alterdatabasemount

Released channel: ORA_DISK_1

Repair failure complete

We don't see the benefits of automatic repair from the above methods, so let's add a little more difficulty and delete all data files (excluding parameter files). Compare it.

one

two

three

four

five

six

seven

eight

nine

SQL > startup

ORACLE instance started.

Total SystemGlobalArea 510554112 bytes

FixedSize 1345968 bytes

VariableSize 171968080 bytes

DatabaseBuffers 331350016 bytes

Redo Buffers 5890048 bytes

ORA-00205: errorinidentifying control file,checkalert logformore

Traditional processing methods, use the following script to restore the database to the startup state, here you need more professional knowledge

one

two

three

four

five

six

seven

Run {

Restore controlfile from autobackup

Alter database mount

Restore database

Recover database until cancel

Alter database open resetlogs

}

Next is the 11g recovery method: list-advise-repair

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

RMAN > list failure

Using targetdatabasecontrol fileinsteadofrecovery catalog

ListofDatabaseFailures

=

Failure ID Priority Status TimeDetected Summary

--

958 CRITICALOPEN 30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'ismissing

915 CRITICALOPEN 30-AUG-16 Control file / u01/app/oracle/oradata/PROD2/control01.ctlismissing

838 CRITICALOPEN 30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'needs media recovery

835 CRITICALOPEN 30-AUG-16 Control file needs media recovery

415 HIGH OPEN 30-AUG-16 Oneormore non-system datafiles are missing

841 HIGH OPEN 30-AUG-16 Oneormore non-system datafiles need media recovery

You can tell us that these files are missing.

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

thirty-four

thirty-five

thirty-six

thirty-seven

thirty-eight

thirty-nine

forty

forty-one

RMAN > advise failure

ListofDatabaseFailures

=

Failure ID Priority Status TimeDetected Summary

--

958 CRITICALOPEN 30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'ismissing

915 CRITICALOPEN 30-AUG-16 Control file / u01/app/oracle/oradata/PROD2/control01.ctlismissing

838 CRITICALOPEN 30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'needs media recovery

835 CRITICALOPEN 30-AUG-16 Control file needs media recovery

415 HIGH OPEN 30-AUG-16 Oneormore non-system datafiles are missing

841 HIGH OPEN 30-AUG-16 Oneormore non-system datafiles need media recovery

Analyzing automatic repair options; this may takesometime

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=20 device type=DISK

Analyzing automatic repair options complete

Notallspecified failures can currently be repaired.

The following failures must be repaired before adviseforothers can be given.

Failure ID Priority Status TimeDetected Summary

--

915 CRITICALOPEN 30-AUG-16 Control file / u01/app/oracle/oradata/PROD2/control01.ctlismissing

Mandatory Manual Actions

=

Nomanual actions available

Optional Manual Actions

=

Nomanual actions available

Automated Repair Options

=

OptionRepair Description

--

1 Use a multiplexed copytorestore control file / u01/app/oracle/oradata/PROD2/control01.ctl

Strategy: The repair includes complete media recoverywithnodata loss

Repair script: / u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_3157315699.hm

Rman has given advice and executed scripts.

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

RMAN > repair failure

Strategy: The repair includes complete media recoverywithnodata loss

Repair script: / u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_3157315699.hm

Contentsofrepair script:

# restore control file using multiplexed copy

Restore controlfilefrom'/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl'

Sql'alter database mount'

Do you really wanttoexecutethe above repair (enter YESorNO)? yes

Executing repair script

Starting restoreat30-AUG-16

Using channel ORA_DISK_1

Channel ORA_DISK_1: copied control file copy

Outputfilename=/u01/app/oracle/oradata/PROD2/control01.ctl

Outputfilename=/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl

Finished restoreat30-AUG-16

Sql statement:alterdatabasemount

Released channel: ORA_DISK_1

Repair failure complete

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

RMAN > list failure

ListofDatabaseFailures

=

Failure ID Priority Status TimeDetected Summary

--

1230 CRITICALOPEN 30-AUG-16 Redo loggroup3isunavailable

1224 CRITICALOPEN 30-AUG-16 Redo loggroup2isunavailable

1218 CRITICALOPEN 30-AUG-16 Redo loggroup1isunavailable

958 CRITICALOPEN 30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'ismissing

838 CRITICALOPEN 30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'needs media recovery

1233 HIGH OPEN 30-AUG-16 Redo log file / u01/app/oracle/oradata/PROD2/redo03.logismissing

1227 HIGH OPEN 30-AUG-16 Redo log file / u01/app/oracle/oradata/PROD2/redo02.logismissing

1221 HIGH OPEN 30-AUG-16 Redo log file / u01/app/oracle/oradata/PROD2/redo01.logismissing

415 HIGH OPEN 30-AUG-16 Oneormore non-system datafiles are missing

841 HIGH OPEN 30-AUG-16 Oneormore non-system datafiles need media recovery

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

thirty-four

thirty-five

thirty-six

thirty-seven

thirty-eight

thirty-nine

forty

RMAN > advise failure

ListofDatabaseFailures

=

Failure ID Priority Status TimeDetected Summary

--

1230 CRITICALOPEN 30-AUG-16 Redo loggroup3isunavailable

1224 CRITICALOPEN 30-AUG-16 Redo loggroup2isunavailable

1218 CRITICALOPEN 30-AUG-16 Redo loggroup1isunavailable

958 CRITICALOPEN 30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'ismissing

838 CRITICALOPEN 30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'needs media recovery

1233 HIGH OPEN 30-AUG-16 Redo log file / u01/app/oracle/oradata/PROD2/redo03.logismissing

1227 HIGH OPEN 30-AUG-16 Redo log file / u01/app/oracle/oradata/PROD2/redo02.logismissing

1221 HIGH OPEN 30-AUG-16 Redo log file / u01/app/oracle/oradata/PROD2/redo01.logismissing

415 HIGH OPEN 30-AUG-16 Oneormore non-system datafiles are missing

841 HIGH OPEN 30-AUG-16 Oneormore non-system datafiles need media recovery

Analyzing automatic repair options; this may takesometime

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=20 device type=DISK

Analyzing automatic repair options complete

Mandatory Manual Actions

=

Nomanual actions available

Optional Manual Actions

=

1. If file / u01/app/oracle/oradata/PROD2/redo03.log was unintentionally renamedormoved, restore it

2. If file / u01/app/oracle/oradata/PROD2/redo02.log was unintentionally renamedormoved, restore it

3. If file / u01/app/oracle/oradata/PROD2/redo01.log was unintentionally renamedormoved, restore it

Automated Repair Options

=

OptionRepair Description

--

1 Perform incompletedatabaserecoverytoSCN 1206859

Strategy: The repair includes point-in-timerecoverywithsomedata loss

Repair script: / u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_3316371170.hm

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

thirty-four

thirty-five

thirty-six

thirty-seven

thirty-eight

thirty-nine

forty

forty-one

forty-two

forty-three

forty-four

forty-five

forty-six

forty-seven

forty-eight

RMAN > repair failure

Strategy: The repair includes point-in-timerecoverywithsomedata loss

Repair script: / u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_3316371170.hm

Contentsofrepair script:

# databasepoint-in-timerecovery

Resetdatabasetoincarnation 5

Restoredatabaseuntil scn 1206859

Recoverdatabaseuntil scn 1206859

Alterdatabaseopenresetlogs

Do you really wanttoexecutethe above repair (enter YESorNO)? YES

Executing repair script

Databaseresettoincarnation 5

Starting restoreat30-AUG-16

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting datafile backupsetrestore

Channel ORA_DISK_1: specifying datafile (s) torestorefrombackupset

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

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

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

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

Channel ORA_DISK_1: readingfrombackup piece / u01/app/oracle/fra/PROD2/backupset/2016_08_24/o1_mf_nnndf_TAG20160824T111405_cvt47yrv_.bkp

Channel ORA_DISK_1: piece handle=/u01/app/oracle/fra/PROD2/backupset/2016_08_24/o1_mf_nnndf_TAG20160824T111405_cvt47yrv_.bkp tag=TAG20160824T111405

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsedtime: 00:00:15

Finished restoreat30-AUG-16

Starting recoverat30-AUG-16

Using channel ORA_DISK_1

Starting media recovery

Archived logforthread 1withsequence3isalreadyondiskasfile / u01/app/oracle/fra/PROD2/archivelog/2016_08_24/o1_mf_1_3_cvt48qv1_.arc

Archived logforthread 1withsequence4isalreadyondiskasfile / u01/app/oracle/fra/PROD2/archivelog/2016_08_24/o1_mf_1_4_cvvbdhx0_.arc

Archived logforthread 1withsequence5isalreadyondiskasfile / u01/app/oracle/fra/PROD2/archivelog/2016_08_30/o1_mf_1_5_cw9m2no2_.arc

Archived log filename=/u01/app/oracle/fra/PROD2/archivelog/2016_08_24/o1_mf_1_3_cvt48qv1_.arc thread=1sequence=3

Archived log filename=/u01/app/oracle/fra/PROD2/archivelog/2016_08_24/o1_mf_1_4_cvvbdhx0_.arc thread=1sequence=4

Archived log filename=/u01/app/oracle/fra/PROD2/archivelog/2016_08_30/o1_mf_1_5_cw9m2no2_.arc thread=1sequence=5

Media recovery complete, elapsedtime: 00:00:02

Finished recoverat30-AUG-16

Databaseopened

Repair failure complete

After reading this article, I believe you have a certain understanding of "RMAN how to quickly restore the database". If you want to know more about it, welcome to follow the industry information channel, thank you for reading!

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.