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

This article is about how to call dbms_backup_restore to restore the database, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

1. View RMAN backup parameters

[oracle@oraserver ~] $rman target /

Recovery Manager: Release 10.2.0.1.0-Production on Mon Aug 15 13:21:54 2011

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

Connected to target database: MYDATA (DBID=305115346)

RMAN > show all

Using target database control file instead of recovery catalog

RMAN configuration parameters 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 ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO'/ orahome/oracle/product/10.2.0/db_1/dbs/snapcf_mydata.f'; # default

RMAN > list backup

2. Perform RMAN backup

1. Full database backup

RMAN > backup database

Starting backup at 15-AUG-11

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: sid=154 devtype=DISK

Channel ORA_DISK_1: starting full datafile backupset

Channel ORA_DISK_1: specifying datafile (s) in backupset

Input datafile fno=00001 name=/oradata/mydata/mydata/system01.dbf

Input datafile fno=00003 name=/oradata/mydata/mydata/sysaux01.dbf

Input datafile fno=00002 name=/oradata/mydata/mydata/undotbs01.dbf

Input datafile fno=00005 name=/oradata/mydata/mydata/example01.dbf

Input datafile fno=00004 name=/oradata/mydata/mydata/users01.dbf

Channel ORA_DISK_1: starting piece 1 at 15-AUG-11

Channel ORA_DISK_1: finished piece 1 at 15-AUG-11

Piece handle=/orahome/flash_recovery_area/MYDATA/backupset/2011_08_15/o1_mf_nnndf_TAG20110815T132445_74kcdxvn_.bkp tag=TAG20110815T132445 comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

Channel ORA_DISK_1: starting full datafile backupset

Channel ORA_DISK_1: specifying datafile (s) in backupset

Including current control file in backupset

Including current SPFILE in backupset

Channel ORA_DISK_1: starting piece 1 at 15-AUG-11

Channel ORA_DISK_1: finished piece 1 at 15-AUG-11

Piece handle=/orahome/flash_recovery_area/MYDATA/backupset/2011_08_15/o1_mf_ncsnf_TAG20110815T132445_74kcgdj8_.bkp tag=TAG20110815T132445 comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04

Finished backup at 15-AUG-11

2. Back up the archive log

RMAN > backup archivelog all delete input

Starting backup at 15-AUG-11

Current log archived

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting archive log backupset

Channel ORA_DISK_1: specifying archive log (s) in backup set

Input archive log thread=1 sequence=3 recid=1 stamp=758254922

Input archive log thread=1 sequence=4 recid=2 stamp=758346126

Input archive log thread=1 sequence=5 recid=3 stamp=758459109

Input archive log thread=1 sequence=6 recid=4 stamp=758570310

Input archive log thread=1 sequence=7 recid=5 stamp=758661373

Input archive log thread=1 sequence=8 recid=6 stamp=758729176

Input archive log thread=1 sequence=9 recid=7 stamp=758733084

Input archive log thread=1 sequence=10 recid=8 stamp=758761235

Input archive log thread=1 sequence=11 recid=9 stamp=758844014

Input archive log thread=1 sequence=12 recid=10 stamp=758930411

Input archive log thread=1 sequence=13 recid=11 stamp=759002811

Input archive log thread=1 sequence=14 recid=12 stamp=759046328

Input archive log thread=1 sequence=15 recid=13 stamp=759146966

Input archive log thread=1 sequence=16 recid=14 stamp=759246991

Channel ORA_DISK_1: starting piece 1 at 15-AUG-11

Channel ORA_DISK_1: finished piece 1 at 15-AUG-11

Piece handle=/orahome/flash_recovery_area/MYDATA/backupset/2011_08_15/o1_mf_annnn_TAG20110815T135631_74kf8k2h_.bkp tag=TAG20110815T135631 comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36

Channel ORA_DISK_1: deleting archive log (s)

Archivelog filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_04/o1_mf_1_3_73m4g9ht_.arc recid=1 stamp=758254922

Archivelog filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_05/o1_mf_1_4_73oxjffh_.arc recid=2 stamp=758346126

Archivelog filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_06/o1_mf_1_5_73scv41h_.arc recid=3 stamp=758459109

Archivelog filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_07/o1_mf_1_6_73wrg5ov_.arc recid=4 stamp=758570310

Archivelog filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_08/o1_mf_1_7_73zkcwh4_.arc recid=5 stamp=758661373

Archivelog filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_09/o1_mf_1_8_741mlq6o_.arc recid=6 stamp=758729176

Archivelog filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_09/o1_mf_1_9_741qdvq6_.arc recid=7 stamp=758733084

Archivelog filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_09/o1_mf_1_10_742lwl6y_.arc recid=8 stamp=758761235

Archivelog filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_10/o1_mf_1_11_7453qfps_.arc recid=9 stamp=758844014

Archivelog filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_11/o1_mf_1_12_747r39d5_.arc recid=10 stamp=758930411

Archivelog filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_12/o1_mf_1_13_749ysv2t_.arc recid=11 stamp=759002811

Archivelog filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_13/o1_mf_1_14_74c99q5d_.arc recid=12 stamp=759046328

Archivelog filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_14/o1_mf_1_15_74gclo7p_.arc recid=13 stamp=759146966

Archivelog filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_15/o1_mf_1_16_74kf8g2z_.arc recid=14 stamp=759246991

Finished backup at 15-AUG-11

RMAN >

3. View controlfile backup

RMAN > list backup of controlfile

List of Backup Sets

=

BS Key Type LV Size Device Type Elapsed Time Completion Time

2 Full 6.80M DISK 00:00:01 15-AUG-11

BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20110815T132445

Piece Name: / orahome/flash_recovery_area/MYDATA/backupset/2011_08_15/o1_mf_ncsnf_TAG20110815T132445_74kcgdj8_.bkp

Control File Included: Ckp SCN: 1223929 Ckp time: 15-AUG-11

RMAN > exit

Recovery Manager complete.

Second, view the path information of database data files, control files and log files through the view

1. View data files

[oracle@oraserver ~] $sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0-Production on Mon Aug 15 14:02:09 2011

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

View data files

SQL > select name from v$datafile

NAME

/ oradata/mydata/mydata/system01.dbf

/ oradata/mydata/mydata/undotbs01.dbf

/ oradata/mydata/mydata/sysaux01.dbf

/ oradata/mydata/mydata/users01.dbf

/ oradata/mydata/mydata/example01.dbf

2. View log files

SQL > col member for A40

SQL > select * from v$logfile

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE

-

3 ONLINE / oradata/mydata/mydata/redo03.log NO

2 ONLINE / oradata/mydata/mydata/redo02.log NO

1 ONLINE / oradata/mydata/mydata/redo01.log NO

3. View control files

SQL > col name for A40

SQL > select * from v$controlfile

STATUS NAME IS_RECOVERY_DEST_FILE BLOCK_SIZE FILE_SIZE_BLKS

/ oradata/mydata/mydata/control01.ctl NO 16384 430

/ oradata/mydata/mydata/control02.ctl NO 16384 430

/ oradata/mydata/mydata/control03.ctl NO 16384 430

Delete all data files, control files and log files

[oracle@oraserver mydata] $pwd

/ oradata/mydata/mydata

[oracle@oraserver mydata] $ll

The total dosage is 1257640

-rw-r- 1 oracle oinstall 7061504 August 15 14:19 control01.ctl

-rw-r- 1 oracle oinstall 7061504 August 15 14:19 control02.ctl

-rw-r- 1 oracle oinstall 7061504 August 15 14:19 control03.ctl

-rw-r- 1 oracle oinstall 104865792 August 15 14:01 example01.dbf

-rw-r- 1 oracle oinstall 52429312 August 15 14:19 redo01.log

-rw-r- 1 oracle oinstall 52429312 August 14 10:09 redo02.log

-rw-r- 1 oracle oinstall 52429312 August 15 13:56 redo03.log

-rw-r- 1 oracle oinstall 325066752 August 15 14:19 sysaux01.dbf

-rw-r- 1 oracle oinstall 513810432 August 15 14:19 system01.dbf

-rw-r- 1 oracle oinstall 20979712 August 13 06:00 temp01.dbf

-rw-r- 1 oracle oinstall 157294592 August 15 14:18 undotbs01.dbf

-rw-r- 1 oracle oinstall 5251072 August 15 14:01 users01.dbf

[oracle@oraserver mydata] $rm-rf *

[oracle@oraserver mydata] $ls

[oracle@oraserver mydata] $

Restore database with dbms_backup_restore package

[oracle@oraserver mydata] $sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0-Production on Mon Aug 15 14:24:57 2011

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

SQL > shutdown abort

ORACLE instance shut down.

SQL > startup

ORACLE instance started.

Total System Global Area 603979776 bytes

Fixed Size 1220796 bytes

Variable Size 180358980 bytes

Database Buffers 415236096 bytes

Redo Buffers 7163904 bytes

ORA-00205: error in identifying control file, check alert log for more info

The control file is lost and the database starts to nomount state.

1. Restore control files

SQL > declare

2 devtype varchar2 (256)

3 done boolean

4 begin

5 devtype:=sys.dbms_backup_restore.deviceallocate (type= >'', ident= > 't1')

6 sys.dbms_backup_restore.restoresetdatafile

7 sys.dbms_backup_restore.restorecontrolfileto (cfname= >'/ oradata/mydata/mydata/control01.ctl')

8 sys.dbms_backup_restore.restorebackuppiece (done= > done,handle= >'/ orahome/flash_recovery_area/MYDATA/backupset/2011_08_15/o1_mf_ncsnf_TAG20110815T132445_74kcgdj8_.bkp')

9 sys.dbms_backup_restore.devicedeallocate

10 end

11 /

PL/SQL procedure successfully completed.

2. Restore data files

SQL > declare

2 devtype varchar2 (256)

3 done boolean

4 begin

5 devtype:=sys.dbms_backup_restore.deviceallocate (type= >'', ident= > 't1')

6 sys.dbms_backup_restore.restoresetdatafile

7 sys.dbms_backup_restore.restoredatafileto (dfnumber= > 01 sys.dbms_backup_restore.restoredatafileto = >'/ oradata/mydata/mydata/system01.dbf')

8 sys.dbms_backup_restore.restoredatafileto (dfnumber= > 02 charge name = >'/ oradata/mydata/mydata/undotbs01.dbf')

9 sys.dbms_backup_restore.restoredatafileto (dfnumber= > 03 sys.dbms_backup_restore.restoredatafileto = >'/ oradata/mydata/mydata/sysaux01.dbf')

10 sys.dbms_backup_restore.restoredatafileto (dfnumber= > 04 camera name = >'/ oradata/mydata/mydata/users01.dbf')

11 sys.dbms_backup_restore.restoredatafileto (dfnumber= > 05 sys.dbms_backup_restore.restoredatafileto Toname = >'/ oradata/mydata/mydata/example01.dbf')

12 sys.dbms_backup_restore.restorebackuppiece (done= > done,handle= >'/ orahome/flash_recovery_area/MYDATA/backupset/2011_08_15/o1_mf_nnndf_TAG20110815T132445_74kcdxvn_.bkp')

13 sys.dbms_backup_restore.devicedeallocate

14 end

15 /

Note: the dfnumber of each dbf file must be the same as the number of each dbf file when there is no failure, which can be queried in select file#,name from v$datafile.

If it is inconsistent, an error will be reported when opening the database after recovery, such as:

SQL >

SQL > alter database mount

Database altered.

SQL > alter database open

Alter database open

*

ERROR at line 1:

ORA-01122: database file 1 failed verification check

ORA-01110: data file 1:'/ oradata/mydata/mydata/system01.dbf'

ORA-01251: Unknown File Header Version read for file number 1

SQL > host ls / oradata/mydata/mydata

Control01.ctl example01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf

[oracle@oraserver mydata] $cp control01.ctl control02.ctl

[oracle@oraserver mydata] $cp control01.ctl control03.ctl

SQL > startup nomount

ORA-01081: cannot start already-running ORACLE-shut it down first

SQL > startup mount

ORA-01081: cannot start already-running ORACLE-shut it down first

SQL > shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL > startup nomount

ORACLE instance started.

Total System Global Area 603979776 bytes

Fixed Size 1220796 bytes

Variable Size 180358980 bytes

Database Buffers 415236096 bytes

Redo Buffers 7163904 bytes

SQL > alter database mount

Database altered.

Declare

Devtype varchar2 (256)

Done boolean

Begin

Devtype:=sys.dbms_backup_restore.deviceallocate (type= >'', ident= > 't1')

Sys.dbms_backup_restore.restoresetarchivedlog (destination= >'/ orahome/flash_recovery_area/MYDATA/archivelog')

Sys.dbms_backup_restore.restorearchivedlog (thread= > 1 sequence = > 3)

Sys.dbms_backup_restore.restorearchivedlog (thread= > 1 sequence = > 4)

Sys.dbms_backup_restore.restorearchivedlog (thread= > 1 sequence = > 5)

Sys.dbms_backup_restore.restorearchivedlog (thread= > 1 sequence = > 6)

Sys.dbms_backup_restore.restorearchivedlog (thread= > 1 sequence = > 7)

Sys.dbms_backup_restore.restorearchivedlog (thread= > 1 sequence = > 8)

Sys.dbms_backup_restore.restorearchivedlog (thread= > 1 sequence = > 9)

Sys.dbms_backup_restore.restorearchivedlog (thread= > 1 sequence = > 10)

Sys.dbms_backup_restore.restorearchivedlog (thread= > 1 sequence = > 11)

Sys.dbms_backup_restore.restorearchivedlog (thread= > 1 sequence = > 12)

Sys.dbms_backup_restore.restorearchivedlog (thread= > 1 sequence = > 13)

Sys.dbms_backup_restore.restorearchivedlog (thread= > 1 sequence = > 14)

Sys.dbms_backup_restore.restorearchivedlog (thread= > 1 sequence = > 15)

Sys.dbms_backup_restore.restorearchivedlog (thread= > 1 sequence = > 16)

Sys.dbms_backup_restore.restorebackuppiece (done= > done,handle= >'/ orahome/flash_recovery_area/MYDATA/backupset/2011_08_15/o1_mf_nnndf_TAG20110815T132445_74kcdxvn_.bkp')

Sys.dbms_backup_restore.devicedeallocate

End

/

SQL > alter database open

Alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Use controlfile to restore the database

SQL > recover database until cancel using backup controlfile

ORA-00279: change 1223908 generated at 08/15/2011 13:24:45 needed for thread 1

ORA-00289: suggestion:

/ orahome/flash_recovery_area/MYDATA/archivelog/2011_08_15/o1_mf_1_16_%u_.arc

ORA-00280: change 1223908 for thread 1 is in sequence # 16

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto

ORA-00279: change 1225044 generated at 08/15/2011 13:56:30 needed for thread 1

ORA-00289: suggestion:

/ orahome/flash_recovery_area/MYDATA/archivelog/2011_08_15/o1_mf_1_17_%u_.arc

ORA-00280: change 1225044 for thread 1 is in sequence # 17

ORA-00278: log file

'/ orahome/flash_recovery_area/MYDATA/archivelog/1_16_758221587.dbf' no longer

Needed for this recovery

ORA-00308: cannot open archived log

'/ orahome/flash_recovery_area/MYDATA/archivelog/2011_08_15/o1_mf_1_17_%u_.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Resetlog opens the database

SQL > alter database open resetlogs

Database altered.

SQL >

The above is how to call dbms_backup_restore to restore the database, the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.