【备份恢复】使用增量备份恢复处于NOARCHIVELOG 模式的数据库

最新推荐文章于 2024-03-05 20:32:54 发布

congmeijiao4803

最新推荐文章于 2024-03-05 20:32:54 发布

阅读量148

点赞数

文章标签：数据库

增量备份前提是有0即全库备份，另外默认的增量备份是指增量差异备份（backup as backupset incremental level 1database），
如果要改为增量累计备份还需要加命令cumulative。

1.修改数据库为非归档模式
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@PROD1>startup mount;
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.

SYS@PROD1>alter database noarchivelog;

Database altered.

SYS@PROD1>archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/backup
Oldest online log sequence 1
Current log sequence 1

SYS@PROD1>alter database open;

Database altered.

2.创建测试用表及数据
SCOTT@PROD1>create table t(x int) tablespace users;

Table created.

SCOTT@PROD1>insert into t values(1);

1 row created.

SCOTT@PROD1>commit;

Commit complete.

3.在noarchivelog模式下，0级备份

SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@PROD1>startup mount;
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.

SYS@PROD1>ed 004
run{
shutdown immediate;
startup mount;
backup as backupset incremental level 0 database;
alter database open;
}

RMAN> run{
2> shutdown immediate;
3> startup mount;
4> backup as backupset incremental level 0 database;
5> alter database open;
6> }

using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes

Starting backup at 21-DEC-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/PROD1/ts_setnew01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-DEC-16
channel ORA_DISK_1: finished piece 1 at 21-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_21/o1_mf_nnnd0_TAG20161221T153652_d5nd8n78_.bkp tag=TAG20161221T153652 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 21-DEC-16

Starting Control File and SPFILE Autobackup at 21-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_21/o1_mf_s_931188647_d5nd93c5_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-DEC-16

database opened

4.在noarchivelog模式下，1级备份
SCOTT@PROD1>conn scott/tiger;
Connected.
SCOTT@PROD1>insert into t values(2);

1 row created.

SCOTT@PROD1>commit;

Commit complete.

SYS@PROD1>ed 005
run{
shutdown immediate;
startup mount;
backup as backupset incremental level 1 database;
alter database open;
}

RMAN> run{
2> shutdown immediate;
3> startup mount;
4> backup as backupset incremental level 1 database;
5> alter database open;
6> }

database closed
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes

Starting backup at 21-DEC-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/PROD1/ts_setnew01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-DEC-16
channel ORA_DISK_1: finished piece 1 at 21-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_21/o1_mf_nnnd1_TAG20161221T154024_d5ndh8dl_.bkp tag=TAG20161221T154024 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 21-DEC-16

Starting Control File and SPFILE Autobackup at 21-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_21/o1_mf_s_931189215_d5ndhcjb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-DEC-16

database opened

6.删除所有控制文件、日志文件、数据文件、参数文件
[oracle@host01 dbs]$ cd /u01/app/oracle/oradata/PROD1/
[oracle@host01 PROD1]$ ls
backup example01.dbf redo02.log sysaux01.dbf temp01.dbf undotbs01.dbf
control01.ctl redo01.log redo03.log system01.dbf ts_setnew01.dbf users01.dbf
[oracle@host01 PROD1]$ rm *

[oracle@host01 PROD1]$ cd /u01/app/oracle/fast_recovery_area/PROD1/
[oracle@host01 PROD1]$ ls control02.ctl
control02.ctl
[oracle@host01 PROD1]$ rm control02.ctl

[oracle@host01 PROD1]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@host01 dbs]$ ls spfilePROD1.ora
spfilePROD1.ora
[oracle@host01 dbs]$ rm spfilePROD1.ora

7.执行恢复
[oracle@host01 ~]$ ps -ef|grep smon
oracle 23667 1 0 15:40 ? 00:00:00 ora_smon_PROD1
oracle 24012 19263 0 15:46 pts/4 00:00:00 grep smon
[oracle@host01 ~]$ kill -9 23667

强制启动到nomount模式
[oracle@host01 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Dec 21 15:46:40 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database (not started)

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initPROD1.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 281019272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5517312 bytes

--恢复参数文件，最好使用最后一次增量备份的备份介质
[oracle@host01 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Dec 21 15:46:40 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database (not started)

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initPROD1.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 281019272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5517312 bytes

RMAN> restore spfile from '/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_21/o1_mf_s_931189215_d5ndhcjb_.bkp';

Starting restore at 21-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_21/o1_mf_s_931189215_d5ndhcjb_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 21-DEC-16

[oracle@host01 dbs]$ ls spfilePROD1.ora
spfilePROD1.ora

完成参数文件的恢复，利用恢复的参数文件启动到nomount模式
SYS@PROD1>shutdown abort;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SYS@PROD1>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@host01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 21 15:49:50 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@PROD1>shutdown abort;
ORACLE instance shut down.
SYS@PROD1>startup nomount;
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes

恢复控制文件，注意：此处恢复控制文件，一定要使用最后一次增量备份的备份介质，否则会因为版本的问题导致恢复出现问题
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_21/o1_mf_s_931189215_d5ndhcjb_.bkp';

Starting restore at 21-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/PROD1/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
Finished restore at 21-DEC-16

控制文件恢复完成，数据库启动到mount模式
SYS@PROD1>alter database mount;

Database altered.

执行全库的还原
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_21/o1_mf_s_931189215_d5ndhcjb_.bkp';

Starting restore at 21-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/PROD1/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
Finished restore at 21-DEC-16

RMAN> restore database;

Starting restore at 21-DEC-16
released channel: ORA_DISK_1
Starting implicit crosscheck backup at 21-DEC-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 16 objects
Finished implicit crosscheck backup at 21-DEC-16

Starting implicit crosscheck copy at 21-DEC-16
using channel ORA_DISK_1
Crosschecked 1 objects
Finished implicit crosscheck copy at 21-DEC-16

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_21/o1_mf_s_931189215_d5ndhcjb_.bkp
File Name: /u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_11_18/o1_mf_s_928252815_d2xf8zpf_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PROD1/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD1/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD1/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/PROD1/ts_setnew01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_21/o1_mf_nnnd0_TAG20161221T153652_d5nd8n78_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_21/o1_mf_nnnd0_TAG20161221T153652_d5nd8n78_.bkp tag=TAG20161221T153652
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 21-DEC-16

由于非归档，且联机重做日志丢失，所以使用noredo子句进行恢复
RMAN> recover database noredo;

Starting recover at 21-DEC-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/PROD1/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/PROD1/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/PROD1/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/PROD1/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/PROD1/example01.dbf
destination for restore of datafile 00006: /u01/app/oracle/oradata/PROD1/ts_setnew01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_21/o1_mf_nnnd1_TAG20161221T154024_d5ndh8dl_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_21/o1_mf_nnnd1_TAG20161221T154024_d5ndh8dl_.bkp tag=TAG20161221T154024
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished recover at 21-DEC-16

恢复完成，resetlogs方式打开数据库，完成所有文件丢失的恢复
RMAN> alter database open resetlogs;

database opened

8.验证在测试前（增量备份前）插入的数据
SYS@PROD1>conn scott/tiger;
Connected.
SCOTT@PROD1>select * from t;

X
----------
1
2

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31400681/viewspace-2131138/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31400681/viewspace-2131138/