| |
There are generally two common database failures
Media damage
Human error
Prevent damage to the media in general use Data Guard technology
Preventing human error, you can use Oracle Flashback feature.
But once the human error can not flash recovery, we need to use Rman restored.
Use Rman recovery, think about the speed of recovery and their own distress as well as the way the tiers of the boss, leadership, colleagues ..
To avoid this situation, you can use another server to do a restore Oracle database.
Every so often the main library, archive logs are pushed to the specified directory restore library (scp, rsync, we are using flowers Beaver monitoring software)
Restore the library from time to time, apply the archive logs.
That is, the data recovery database is always behind the production database. Similar to a delay of DataGuard.
Restore our library configuration behind the production database 6-9 hours.
Recover from human error to restore the library, it can generally be controlled within 30 minutes.
Rman recovering from a different machine, estimated at 4-5 hours.
Production restore library generally have the following steps
1. Uninstall Restore library already existing database (if reducing inventory database instance)
shutdown abort;
startup mount exclusive restrict;
alter system enable restricted session;
drop database;
2. Rman backup of the production database
CONFIGURE DEVICE TYPE DISK PARALLELISM 5 BACKUP TYPE TO BACKUPSET;
sql 'alter system archive log current';
BACKUP AS COMPRESSED BACKUPSET DATABASE;
3. Under $ ORACLE_HOME / dbs directory, create a file (init $ SID.ora)
There is only one line in the configuration file SID
db_name = mvbox
4. Restore spfile
Start Rman backup, restore spfile
[Oracle @ localhost / data / IP / 2015_09_25] $ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Sep 25 17:26:40 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 171966464 bytes
Fixed Size 2082496 bytes
Variable Size 113248576 bytes
Database Buffers 50331648 bytes
Redo Buffers 6303744 bytes
RMAN> restore spfile to '/home/oracle/app/oracle/product/10.2.0/db_1/dbs/spfilemvbox.ora' from '/data/IP/2015_09_25/o1_mf_nnsnf_TAG20150925T134231_c09s9rq2_.bkp';
Starting restore at 25-SEP-15
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: /data/IP/2015_09_25/o1_mf_nnsnf_TAG20150925T134231_c09s9rq2_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 25-SEP-15
Convert the spfile pfile, then modify the associated directory
create pfile = '/ tmp / pfile.ora' from spfile;
vim /tmp/pfile.ora mainly modify the path
Then use the pfile to start the database nomount state, and then generate the modified spfile pfile
5. Restore the control file
RMAN> restore controlfile from '/data/IP/2015_09_25/o1_mf_ncnnf_TAG20150925T134231_c09s9pms_.bkp';
Starting restore at 25-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid = 1640 devtype = DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename = / data / oracledata / flash_recovery_area / control01.ctl
Finished restore at 25-SEP-15
RMAN> shutdown
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 53687091200 bytes
Fixed Size 2184048 bytes
Variable Size 4546628752 bytes
Database Buffers 49123688448 bytes
Redo Buffers 14589952 bytes
RMAN>
6. Restore the data files, and restore
The production database archive logs are copied to the specified directory
RMAN> catalog start with '/ data / IP';
Production database files are placed in / dbdata / oracledata / mvbox / mount point
And restore the database data files in / data / oracledata / oradata / mvbox / mount point
In the production database to run the query
select
'Set newname for datafile' || file_id ||
'To' '' ||
replace (file_name, '/ dbdata / oracledata / mvbox', '/ data / oracledata / oradata / mvbox') || '' ';'
from dba_data_files;
Then according to the results of this query restore command library to perform the spell
To restore data files
run {
CONFIGURE DEVICE TYPE DISK PARALLELISM 5 BACKUP TYPE TO BACKUPSET;
set newname for datafile 4 to '/data/oracledata/oradata/mvbox/users01.dbf';
set newname for datafile 3 to '/data/oracledata/oradata/mvbox/sysaux01.dbf';
set newname for datafile 1 to '/data/oracledata/oradata/mvbox/system01.dbf';
set newname for datafile 5 to '/data/oracledata/oradata/mvbox/system02.dbf';
set newname for datafile 6 to '/data/oracledata/oradata/mvbox/sysaux02.dbf';
set newname for datafile 7 to '/data/oracledata/oradata/mvbox/users02.dbf';
set newname for datafile 8 to '/data/oracledata/oradata/mvbox/users03.dbf';
set newname for datafile 9 to '/data/oracledata/oradata/mvbox/users04.dbf';
set newname for datafile 10 to '/data/oracledata/oradata/mvbox/users05.dbf';
set newname for datafile 12 to '/data/oracledata/oradata/mvbox/users06.dbf';
set newname for datafile 13 to '/data/oracledata/oradata/mvbox/system03.dbf';
set newname for datafile 14 to '/data/oracledata/oradata/mvbox/sysaux03.dbf';
set newname for datafile 27 to '/data/oracledata/oradata/mvbox/users19.dbf';
set newname for datafile 15 to '/data/oracledata/oradata/mvbox/users07.dbf';
set newname for datafile 16 to '/data/oracledata/oradata/mvbox/users08.dbf';
set newname for datafile 17 to '/data/oracledata/oradata/mvbox/users09.dbf';
set newname for datafile 18 to '/data/oracledata/oradata/mvbox/users10.dbf';
set newname for datafile 19 to '/data/oracledata/oradata/mvbox/users11.dbf';
set newname for datafile 20 to '/data/oracledata/oradata/mvbox/users12.dbf';
set newname for datafile 21 to '/data/oracledata/oradata/mvbox/users13.dbf';
set newname for datafile 22 to '/data/oracledata/oradata/mvbox/users14.dbf';
set newname for datafile 23 to '/data/oracledata/oradata/mvbox/users15.dbf';
set newname for datafile 24 to '/data/oracledata/oradata/mvbox/users16.dbf';
set newname for datafile 25 to '/data/oracledata/oradata/mvbox/users17.dbf';
set newname for datafile 26 to '/data/oracledata/oradata/mvbox/users18.dbf';
set newname for datafile 28 to '/data/oracledata/oradata/mvbox/users20.dbf';
set newname for datafile 33 to '/data/oracledata/oradata/mvbox/users21.dbf';
set newname for datafile 34 to '/data/oracledata/oradata/mvbox/users22.dbf';
set newname for datafile 35 to '/data/oracledata/oradata/mvbox/undotbs.dbf';
restore database;
switch datafile all;
}
Found transmitted over the production database archive log files to find the last file
Restored to the last log file archiving sequence.
run {
CONFIGURE DEVICE TYPE DISK PARALLELISM 5 BACKUP TYPE TO BACKUPSET;
set until sequence 7939;
recover database;
}
Then open the database
The library has a production block change tracking
Restore library Close
In addition, the time point of view recovery
alter database disable block change tracking;
alter database open read only;
select to_char (scn_to_timestamp (CURRENT_SCN), 'yyyy-mm-dd HH24: mi: ss') ts from v $ database;
At this time, the library has been set up to restore completed.
Production library has a program, every 30 minutes, will be transmitted over the archive logs.
Restore library here, you only need to restore a regular basis (six hours to recover before)
0 * / 3 * * * /root/testoracle.sh
#! / Bin / bash
source ~ / .bashrc
rman target / << EOF
shutdown
startup mount
catalog start with '/ data / IP' noprompt;
recover database until time "sysdate - interval '6' hour";
sql 'alter database open read only';
EOF
7. Restore the read-write mode Kula play.
If the seventh step, there is trouble.
This means that the production database hang, DataGuard backup machine hung up.
We can only temporarily restore Kura up as a production library.
Try to stay in 16:00 restore database restore to 18:00, and pulled up for the read-write mode.
Before restoration
RMAN command execution
rman:
shutdown;
startup mount;
catalog start with '/ data / IP' noprompt;
recover database until time "to_date ( '20150925 18:00:00', 'yyyymmdd hh24: mi: ss')";
RMAN performs
RMAN> shutdown;
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 53687091200 bytes
Fixed Size 2184048 bytes
Variable Size 4546628752 bytes
Database Buffers 49123688448 bytes
Redo Buffers 14589952 bytes
RMAN> catalog start with '/ data / IP' noprompt;
searching for all files that match the pattern / data / IP
List of Files Unknown to the Database
=====================================
File Name: /data/IP/archivelog/2015_09_25/o1_mf_1_7945_c0bh8dgv_.arc
File Name: /data/IP/archivelog/2015_09_25/o1_mf_1_7942_c0bc4hv5_.arc
File Name: /data/IP/archivelog/2015_09_25/o1_mf_1_7944_c0bfx7l2_.arc
File Name: /data/IP/archivelog/2015_09_25/o1_mf_1_7940_c0b7ntyf_.arc
File Name: /data/IP/archivelog/2015_09_25/o1_mf_1_7941_c0b9kdlw_.arc
File Name: /data/IP/archivelog/2015_09_25/o1_mf_1_7943_c0bdkzyp_.arc
cataloging files ...
cataloging done
List of Cataloged Files
=======================
File Name: /data/IP/archivelog/2015_09_25/o1_mf_1_7945_c0bh8dgv_.arc
File Name: /data/IP/archivelog/2015_09_25/o1_mf_1_7942_c0bc4hv5_.arc
File Name: /data/IP/archivelog/2015_09_25/o1_mf_1_7944_c0bfx7l2_.arc
File Name: /data/IP/archivelog/2015_09_25/o1_mf_1_7940_c0b7ntyf_.arc
File Name: /data/IP/archivelog/2015_09_25/o1_mf_1_7941_c0b9kdlw_.arc
File Name: /data/IP/archivelog/2015_09_25/o1_mf_1_7943_c0bdkzyp_.arc
RMAN> recover database until time "to_date ( '20150925 18:00:00', 'yyyymmdd hh24: mi: ss')";
Starting recover at 25-SEP-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid = 1641 devtype = DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid = 1639 devtype = DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid = 1638 devtype = DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid = 1637 devtype = DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: sid = 1636 devtype = DISK
starting media recovery
archive log thread 1 sequence 7939 is already on disk as file /data/IP/archivelog/2015_09_25/o1_mf_1_7939_c0b5fr87_.arc
archive log thread 1 sequence 7940 is already on disk as file /data/IP/archivelog/2015_09_25/o1_mf_1_7940_c0b7ntyf_.arc
archive log filename = / data / IP / archivelog / 2015_09_25 / o1_mf_1_7939_c0b5fr87_.arc thread = 1 sequence = 7939
archive log filename = / data / IP / archivelog / 2015_09_25 / o1_mf_1_7940_c0b7ntyf_.arc thread = 1 sequence = 7940
media recovery complete, elapsed time: 00:04:07
Finished recover at 25-SEP-15
Enter sqlplus, modify the location of the online redo log, because this location or production library, not necessarily the path to restore the library.
SQL> select 'alter database rename file' '' || member || '' 'to' '/ data / oracledata / oradata / mvbox / redo' || rownum || '.log' ';' sql from v $ logfile ;
SQL
alter database rename file '/data/oracledata/oradata/mvbox/redo04.log1' to '/data/oracledata/oradata/mvbox/redo1.log';
alter database rename file '/data/dblog/redo04.log2' to '/data/oracledata/oradata/mvbox/redo2.log';
alter database rename file '/data/oracledata/oradata/mvbox/redo05.log3' to '/data/oracledata/oradata/mvbox/redo3.log';
alter database rename file '/data/dblog/redo05.log4' to '/data/oracledata/oradata/mvbox/redo4.log';
alter database rename file '/data/oracledata/oradata/mvbox/redo06.log5' to '/data/oracledata/oradata/mvbox/redo5.log';
alter database rename file '/data/dblog/redo06.log6' to '/data/oracledata/oradata/mvbox/redo6.log';
alter database rename file '/data/oracledata/oradata/mvbox/redo07.log7' to '/data/oracledata/oradata/mvbox/redo7.log';
alter database rename file '/data/dblog/redo07.log8' to '/data/oracledata/oradata/mvbox/redo8.log';
alter database rename file '/data/oracledata/oradata/mvbox/redo08.log9' to '/data/oracledata/oradata/mvbox/redo9.log';
alter database rename file '/data/dblog/redo08.log10' to '/data/oracledata/oradata/mvbox/redo10.log';
10 rows selected.
alter database rename file '/data/oracledata/oradata/mvbox/redo04.log1' to '/data/oracledata/oradata/mvbox/redo1.log';
Database altered.
alter database rename file '/data/dblog/redo04.log2' to '/data/oracledata/oradata/mvbox/redo2.log';
Database altered.
alter database rename file '/data/oracledata/oradata/mvbox/redo05.log3' to '/data/oracledata/oradata/mvbox/redo3.log';
Database altered.
alter database rename file '/data/dblog/redo05.log4' to '/data/oracledata/oradata/mvbox/redo4.log';
Database altered.
alter database rename file '/data/oracledata/oradata/mvbox/redo06.log5' to '/data/oracledata/oradata/mvbox/redo5.log';
Database altered.
alter database rename file '/data/dblog/redo06.log6' to '/data/oracledata/oradata/mvbox/redo6.log';
Database altered.
alter database rename file '/data/oracledata/oradata/mvbox/redo07.log7' to '/data/oracledata/oradata/mvbox/redo7.log';
Database altered.
alter database rename file '/data/dblog/redo07.log8' to '/data/oracledata/oradata/mvbox/redo8.log';
Database altered.
alter database rename file '/data/oracledata/oradata/mvbox/redo08.log9' to '/data/oracledata/oradata/mvbox/redo9.log';
Database altered.
SQL> alter database rename file '/data/dblog/redo08.log10' to '/data/oracledata/oradata/mvbox/redo10.log';
Database altered.
SQL> alter database open resetlogs;
Database altered.
SQL>
Although the database has now pulled up, but note that there are temporary table space.
Default temporary table space to add a data file.
Then remove other data files, re-add
(Backup does not include the temporary table space, then pulled up require additional temporary table space to handle it)
ALTER TABLESPACE TEMP
ADD TEMPFILE '/data/oracledata/oradata/mvbox/temp03.dbf'
SIZE 4G
AUTOEXTEND ON
NEXT 128M
MAXSIZE 30G;
ALTER TABLESPACE TEMP DROP TEMPFILE '/dbdata/oracledata/mvbox/temp01.dbf';
ALTER TABLESPACE TEMP DROP TEMPFILE '/dbdata/oracledata/mvbox/temp02.dbf';
Restore library completed structures. |
|
|
|