Site recommendations for backup protection before Oracle Database exception recovery

Source: Internet
Author: User
Tags oracle database


Whether it's at conferences, or when friends/netizens privately consult Oracle database restores, I stress that if you're not sure, you should make a backup of your site to make sure there are no two damage to the site. You cannot restore the database, but you cannot destroy the database again, making it more difficult to recover two times. Here are some guidelines and simple scripts for pre-restore backup, which I hope will help.

Which files need to be backed up

Familiar with database recovery friends may be the case, Oracle in the process of abnormal recovery is mainly modified in the system table space data, other data files, redo data, control files (of course, because of Redo,undo other data files inside the block may also change). In the case of backup time, backup space is allowed, is all of these files backed up as well

Full backup files

Set Lines 150
Set pages 10000
Select name from V$datafile
UNION ALL
Select name from V$controlfile
UNION ALL
Select member from V$logfile;

In some cases: for example, if all backups are too long and backup space is low, how do we back up and minimize the damage to the original environment due to abnormal recovery? Backup the core system tablespaces, data file headers, redo File,control file, Because this is not a simple copy operation, so the backup statement generated at the same time, also generate a RESTORE statement, must not generate a backup statement, no recovery statements, resulting in the back of the restoration of the difficulty of the site increase.

Unable to fully backup Linux/unix database before recovery

Set Lines 150
Set pages 10000
Select ' dd if= ' | | name| | ' of=&&back_dir/' | | ts#| | ' _'|| file#| | '. DBF bs=1048576 count=10 '
From V$datafile where ts#<>0
UNION ALL
Select ' dd if= ' | | name| | ' of=&&back_dir/' | | ts#| | ' _'|| file#| | '. DBF ' from V$datafile where ts#=0
UNION ALL
Select ' dd if= ' | | name| | ' of=&&back_dir/control0 ' | | rownum| | '. CTL ' from V$controlfile
UNION ALL
Select ' dd if= ' | | member| | ' of=&&back_dir/' | | thread#| | ' _'|| a.group#| | ' _'|| sequence#| | ' _'|| SUBSTR (Member,
InStr (Member, '/', -1) +1 "from V$log A, v$logfile b WHERE a.group# = b.group#;

Unable to fully Linux/unix use Backup restore

Set Lines 150
Set pages 1000
Select ' dd of= ' | | name| | ' if=&&back_dir/' | | ts#| | ' _'|| file#| | '. DBF bs=1048576 count=10 Conv=notrunc '
From V$datafile where ts#<>0
UNION ALL
Select ' dd if= ' | | name| | ' if=&&back_dir/' | | ts#| | ' _'|| file#| | '. DBF ' from V$datafile where ts#=0
UNION ALL
Select ' dd of= ' | | name| | ' if=&&back_dir/control0 ' | | rownum| | '. CTL ' from V$controlfile
UNION ALL
Select ' dd of= ' | | member| | ' if=&&back_dir/' | | thread#| | ' _'|| a.group#| | ' _'|| sequence#| | ' _'|| SUBSTR (Member,
InStr (Member, '/', -1) +1 "from V$log A, v$logfile b WHERE a.group# = b.group#;

Because the win path slash is different (/and the difference), the win BACKUP statement cannot be fully prepared

Set Lines 150
Set pages 10000
Select ' dd if= ' | | name| | ' of=&&back_dir\ ' | | ts#| | ' _'|| file#| | '. DBF bs=1048576 count=10 '
From V$datafile where ts#<>0
UNION ALL
Select ' dd if= ' | | name| | ' of=&&back_dir\ ' | | ts#| | ' _'|| file#| | '. DBF ' from V$datafile where ts#=0
UNION ALL
Select ' dd if= ' | | name| | ' of=&&back_dir\control0 ' | | rownum| | '. CTL ' from V$controlfile
UNION ALL
Select ' dd if= ' | | member| | ' of=&&back_dir\ ' | | thread#| | ' _'|| a.group#| | ' _'|| sequence#| | ' _'|| SUBSTR (Member,
InStr (Member, ' \ ", -1) +1) from V$log A, v$logfile b WHERE a.group# = b.group#;

Win RESTORE statement when not fully prepared

Set Lines 150
Set pages 1000
Select ' dd of= ' | | name| | ' if=&&back_dir\ ' | | ts#| | ' _'|| file#| | '. DBF bs=1048576 count=10 Conv=notrunc '
From V$datafile where ts#<>0
UNION ALL
Select ' dd if= ' | | name| | ' if=&&back_dir\ ' | | ts#| | ' _'|| file#| | '. DBF ' from V$datafile where ts#=0
UNION ALL
Select ' dd of= ' | | name| | ' if=&&back_dir\control0 ' | | rownum| | '. CTL ' from V$controlfile
UNION ALL
Select ' dd of= ' | | member| | ' if=&&back_dir\ ' | | thread#| | ' _'|| a.group#| | ' _'|| sequence#| | ' _'|| SUBSTR (Member,
InStr (Member, ' \ ", -1) +1) from V$log A, v$logfile b WHERE a.group# = b.group#;

The backup of the ASM disk is primarily the backup disk head 100M space and is backed up directly using the DD command

ASM Backup
Set Lines 150
Set pages 1000
Select ' dd if= ' | | path| | ' of=&asmbackup_dir/' | | group_number| | ' _'|| disk_number| | '. ASM bs=1048576
Count=100 ' from V$asm_disk;

ASM Recovery

Set Lines 150
Set pages 1000
Select ' dd of= ' | | path| | ' if=&asmbackup_dir/' | | group_number| | ' _'|| disk_number| | '. ASM bs=1048576
Count=100 Conv=notrunc ' from V$asm_disk;
Asmlib need to note the ORCL: Replace with the/dev/oracleasm/disks/directory. In addition, the DD command program DD in WIN environment

Note: For ASM, if the ASM disk group is properly mount, and the database cannot open the exception to restore, please do not refer to the backup situation, please see the following article, stay tuned

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.