Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle Database Restore     - How to upgrade to Ubuntu 14.04 Linux Kernel 4.4.1 LTS (Linux)

- RHEL6.4 one key installation Redmine (Linux)

- Oracle 10g New Features - Archive Compression (Database)

- MySQL Tutorial: Some optimized for I / O memory areas (Database)

- 10 example of the detection memory usage free Linux commands (Linux)

- Debian GNU / Linux service list acquisition, shutting down services or run (Linux)

- MySQL Parameter Tuning Best Practices (Database)

- Oracle database import and export (Database)

- OpenNMS separate database (Server)

- Linux System Getting Started Learning: Disable HTTP forwarding wget in (Linux)

- Use mysqldump backup performed MariaDB (Database)

- Java, hashcode, equals and == (Programming)

- Linux system package manager -RPM (Linux)

- Linux common network tools: traceroute routing of scanned (Linux)

- Large computer network security policy Experience (Linux)

- To create a secure network firewall with iptables Under Linux (Linux)

- Ubuntu install the camera driver (Linux)

- Based on Python: OpenCV simple image manipulation (Programming)

- Python object-oriented programming (Programming)

- Help you make Git Bisect (Linux)

 
         
  Oracle Database Restore
     
  Add Date : 2018-11-21      
         
       
         
  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.
     
         
       
         
  More:      
 
- MySQL Parameter Tuning Best Practices (Database)
- Use HugePages optimize memory performance (Database)
- Oracle database physical file backup / restore (Database)
- GNU Linux use diff to generate a patch with the patch (Linux)
- Postgres-X2 deployment steps (Database)
- Oracle 10046 Event (Database)
- Flask deploy applications using Nginx on Ubuntu (Server)
- An Analysis of the C Algorithm for Calculating the Number of Days Between Date (Programming)
- 30 Practical Linux system administrators will learn the command (Linux)
- Linux alpine use the command line to access Gmail (Linux)
- Spring declarative transaction management (Programming)
- Sleuth Kit: used to analyze a disk image and restore files open source forensics tools (Linux)
- Linux Platform Oracle 11g Single Instance Installation Deployment Configuration Quick Reference (Database)
- Linux Programming memory mapping (Programming)
- Vagrant Getting Start (Linux)
- Linux NFS FTP use (Server)
- How to use the character in C ++ without pressing the Enter key to enter the Show (Programming)
- Install Jetty on CentOS / RHEL 6.X (Server)
- Linux system security check notes on performance (Linux)
- To share some very useful Vim command (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.