oracle归档语句,oracle 归档管理

一、归档模式与非归档模式区别

非归档，联机日志文件循环覆盖，数据库执行recover时所需要的归档不存在，数据库不能进行完全恢复。

二、更改数据库的归档模式

1 shutdown the database instance

shutdown immediate;

2 start the database mount

startup mount

3 change the database archiving mode

alter database archivelog;

4 open the database

alter database open;

eg：

SQL> select log_mode from v$database;

LOG_MODE

NOARCHIVELOG

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /oracle/crm/disk2/archive

Oldest online log sequence 34

Current log sequence 36

SQL> select dest_name,status,destination from v$archive_dest;

DEST_NAME STATUS DESTINATION

LOG_ARCHIVE_DEST_1 VALID /oracle/archivelog

LOG_ARCHIVE_DEST_2 ALTERNATE /oracle/crm/disk2/archive

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 952020992 bytes

Fixed Size 2232208 bytes

Variable Size 578814064 bytes

Database Buffers 364904448 bytes

Redo Buffers 6070272 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /oracle/crm/disk2/archive

Oldest online log sequence 34

Next log sequence to archive 36

Current log sequence 36

三、 调整归档的进程数

SQL> alter system set log_archive_max_processes=6;

System altered.

四、设置归档目录

1 参数LOG_ARCHIVE_DEST_n n由1到10用于指定本地或远程归档目录，n值由11到31仅用于指定远程归档目录

2 LOG_ARCHIVE_DEST_STATE_n (where n is an integer from 1 to 31)

enable:表示该目录数据库可以使用

defer:表示该目录被临时禁止

alternate:表示该目录处于替换状态，如果主目录不可用，这该目录变为enable。同时需要注意的是alternate不能用于log_archive_dest_11 to log_archive_dest_31

eg

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

SQL> col dest_name for a30

SQL> col status for a15

SQL> col destination for a50

SQL> select dest_name,status,destination from v$archive_dest;

DEST_NAME STATUS DESTINATION

LOG_ARCHIVE_DEST_1 VALID /oracle/archivelog

LOG_ARCHIVE_DEST_2 DEFERRED /oracle/crm/disk2/archive

3 指定归档目录语句

alter system set LOG_ARCHIVE_DEST_1 = 'LOCATION = /disk1/archive'

alter system set LOG_ARCHIVE_DEST_2 = 'LOCATION = /disk2/archive'

alter system set LOG_ARCHIVE_DEST_3 = 'LOCATION = +RECOVERY'

4 指定归档名字

alter system set log_archiveformat='arch%t%s%r.arch' scope=spfile;

%t:thread number

%s:log sequence number

%r: the resetlogs ID

5 查询数据库当前设置的归档目录

select dest_name,status,destination from v$archive_dest;

DEST_NAME STATUS DESTINATION

LOG_ARCHIVE_DEST_1 VALID /oracle/archivelog

LOG_ARCHIVE_DEST_2 ALTERNATE /oracle/crm/disk2/archive

五、归档的备份

1 在rman下备份归档

RMAN> backup archivelog from sequence 64 format '/backup/archbk%T%U.bak';

Starting backup at 18-MAY-14

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=64 RECID=65 STAMP=847887432

input archived log thread=1 sequence=65 RECID=66 STAMP=847887434

input archived log thread=1 sequence=66 RECID=67 STAMP=847887435

input archived log thread=1 sequence=67 RECID=68 STAMP=847887436

input archived log thread=1 sequence=68 RECID=69 STAMP=847887436

input archived log thread=1 sequence=69 RECID=70 STAMP=847887440

input archived log thread=1 sequence=70 RECID=71 STAMP=847887442

input archived log thread=1 sequence=71 RECID=72 STAMP=847887442

input archived log thread=1 sequence=72 RECID=73 STAMP=847887443

input archived log thread=1 sequence=73 RECID=74 STAMP=847887445

input archived log thread=1 sequence=74 RECID=75 STAMP=847887446

input archived log thread=1 sequence=75 RECID=76 STAMP=847887446

input archived log thread=1 sequence=76 RECID=77 STAMP=847887447

input archived log thread=1 sequence=77 RECID=78 STAMP=847887448

input archived log thread=1 sequence=78 RECID=79 STAMP=847887513

input archived log thread=1 sequence=79 RECID=80 STAMP=847888655

channel ORA_DISK_1: starting piece 1 at 18-MAY-14

channel ORA_DISK_1: finished piece 1 at 18-MAY-14

piece handle=/backup/archbk_20140518_0cp8jg8f_1_1.bak tag=TAG20140518T123735 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 18-MAY-14

2 通过plus archivelog 备份归档

命令 backup database plus archivelog format '/backup/fullbk%U%T.bak';

该命令执行如下动作

1、 Runs the ALTER SYSTEM ARCHIVE LOG CURRENT statement

2、 Runs BACKUP ARCHIVELOG ALL. If backup optimization is enabled, then RMAN skips logs that it has already backed up to the specified device.

3 、Backs up the rest of the files specified in the BACKUP command

4、Backs up any remaining archived logs generated during the backup. If backup optimization is not enabled, then RMAN backs up the logs generated in Step 1

plus all the logs generated during the backup

/×备份所有剩余的归档以及在备份时新生成的归档/

eg

RMAN> backup database plus archivelog format '/backup/fullbk%U%T.bak';

Starting backup at 18-MAY-14

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=64 RECID=115 STAMP=847890752

input archived log thread=1 sequence=65 RECID=118 STAMP=847890752

input archived log thread=1 sequence=66 RECID=119 STAMP=847890752

input archived log thread=1 sequence=67 RECID=120 STAMP=847890752

input archived log thread=1 sequence=68 RECID=121 STAMP=847890752

input archived log thread=1 sequence=69 RECID=117 STAMP=847890752

input archived log thread=1 sequence=70 RECID=122 STAMP=847890752

input archived log thread=1 sequence=71 RECID=123 STAMP=847890752

input archived log thread=1 sequence=72 RECID=124 STAMP=847890752

input archived log thread=1 sequence=73 RECID=125 STAMP=847890752

input archived log thread=1 sequence=74 RECID=126 STAMP=847890752

input archived log thread=1 sequence=75 RECID=127 STAMP=847890752

input archived log thread=1 sequence=76 RECID=128 STAMP=847890752

input archived log thread=1 sequence=77 RECID=129 STAMP=847890752

input archived log thread=1 sequence=78 RECID=116 STAMP=847890752

input archived log thread=1 sequence=79 RECID=114 STAMP=847890752

input archived log thread=1 sequence=80 RECID=113 STAMP=847889781

input archived log thread=1 sequence=81 RECID=130 STAMP=847898077

input archived log thread=1 sequence=82 RECID=131 STAMP=847898081

input archived log thread=1 sequence=83 RECID=132 STAMP=847898082

input archived log thread=1 sequence=84 RECID=133 STAMP=847898083

input archived log thread=1 sequence=85 RECID=134 STAMP=847898084

input archived log thread=1 sequence=86 RECID=135 STAMP=847898087

input archived log thread=1 sequence=87 RECID=136 STAMP=847898190

input archived log thread=1 sequence=88 RECID=137 STAMP=847898333

input archived log thread=1 sequence=89 RECID=138 STAMP=847898714

input archived log thread=1 sequence=90 RECID=139 STAMP=847899333

input archived log thread=1 sequence=91 RECID=140 STAMP=847900304

input archived log thread=1 sequence=92 RECID=141 STAMP=847900930

channel ORA_DISK_1: starting piece 1 at 18-MAY-14

channel ORA_DISK_1: finished piece 1 at 18-MAY-14

piece handle=/backup/fullbk_0dp8js84_1_1_20140518.bak tag=TAG20140518T160211 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 18-MAY-14

Starting backup at 18-MAY-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/oracle/test/system1.dbf

input datafile file number=00003 name=/oracle/test/sysaux01.dbf

input datafile file number=00010 name=/oracle/test/undotbs5.dbf

input datafile file number=00004 name=/oracle/test/users01.dbf

input datafile file number=00013 name=/oracle/test/test2.dbf

channel ORA_DISK_1: starting piece 1 at 18-MAY-14

channel ORA_DISK_1: finished piece 1 at 18-MAY-14

piece handle=/oracle/CRM2/app/product/11.2.0/db1/dbs/0ep8js89_1_1 tag=TAG20140518T160215 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:03:26

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 18-MAY-14

channel ORA_DISK_1: finished piece 1 at 18-MAY-14

piece handle=/oracle/CRM2/app/product/11.2.0/db1/dbs/0fp8jseo_1_1 tag=TAG20140518T160215 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 18-MAY-14

Starting backup at 18-MAY-14

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=93 RECID=142 STAMP=847901149

channel ORA_DISK_1: starting piece 1 at 18-MAY-14

channel ORA_DISK_1: finished piece 1 at 18-MAY-14

piece handle=/backup/fullbk_0gp8jseu_1_1_20140518.bak tag=TAG20140518T160550 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 18-MAY-14

六、 关于archivelog delete input 和delete all input区别

1 rman的归档位置定义两个

log_archive_dest_1 string LOCATION=/oracle/archive

log_archive_dest_2 string LOCATION=/oracle/archive2/

2 执行alter system switch logfile命令后两个归档目录下的归档日志

[oracle@oracle archive]$ ls -lt

total 2648

-rw-r----- 1 oracle oinstall 1024 Aug 17 16:29 1_38_791488634.dbf

-rw-r----- 1 oracle oinstall 1536 Aug 17 16:29 1_37_791488634.dbf

-rw-r----- 1 oracle oinstall 1536 Aug 17 16:29 1_35_791488634.dbf

-rw-r----- 1 oracle oinstall 1024 Aug 17 16:29 1_36_791488634.dbf

-rw-r----- 1 oracle oinstall 5120 Aug 17 16:26 1_34_791488634.dbf

-rw-r----- 1 oracle oinstall 59392 Aug 17 16:23 1_33_791488634.dbf

[root@oracle archive2]# ls -lt

total 88

-rw-r----- 1 oracle oinstall 1024 Aug 17 16:29 1_38_791488634.dbf

-rw-r----- 1 oracle oinstall 1536 Aug 17 16:29 1_37_791488634.dbf

-rw-r----- 1 oracle oinstall 1536 Aug 17 16:29 1_35_791488634.dbf

-rw-r----- 1 oracle oinstall 1024 Aug 17 16:29 1_36_791488634.dbf

-rw-r----- 1 oracle oinstall 5120 Aug 17 16:26 1_34_791488634.dbf

-rw-r----- 1 oracle oinstall 59392 Aug 17 16:23 1_33_791488634.dbf

3 执行

RMAN> backup database plus archivelog delete input; 查看日志可以发现oracle删除的归档日志仅仅是其备份过的。

archived log file name=/oracle/archive2/1_33_791488634.dbf RECID=321 STAMP=791569408

archived log file name=/oracle/archive/1_34_791488634.dbf RECID=322 STAMP=791569592

archived log file name=/oracle/archive/1_35_791488634.dbf RECID=324 STAMP=791569775

archived log file name=/oracle/archive2/1_36_791488634.dbf RECID=327 STAMP=791569775

archived log file name=/oracle/archive2/1_37_791488634.dbf RECID=329 STAMP=791569778

archived log file name=/oracle/archive/1_38_791488634.dbf RECID=330 STAMP=791569780

archived log file name=/oracle/archive/1_39_791488634.dbf RECID=332 STAMP=791570012

archived log file name=/oracle/archive/1_40_791488634.dbf RECID=334 STAMP=791570187

注意删除的归档日志位置随机有archive2 也有archive。

4 重新切换生成归档日志

[oracle@oracle archive]$ ls -lt

total 2684

-rw-r----- 1 oracle oinstall 1024 Aug 17 16:53 1_46_791488634.dbf

-rw-r----- 1 oracle oinstall 1536 Aug 17 16:53 1_44_791488634.dbf

-rw-r----- 1 oracle oinstall 1024 Aug 17 16:53 1_45_791488634.dbf

-rw-r----- 1 oracle oinstall 1024 Aug 17 16:53 1_42_791488634.dbf

-rw-r----- 1 oracle oinstall 1024 Aug 17 16:53 1_43_791488634.dbf

-rw-r----- 1 oracle oinstall 32768 Aug 17 16:53 1_41_791488634.dbf

[root@oracle archive2]# ls -lt

total 92

-rw-r----- 1 oracle oinstall 1024 Aug 17 16:53 1_46_791488634.dbf

-rw-r----- 1 oracle oinstall 1536 Aug 17 16:53 1_44_791488634.dbf

-rw-r----- 1 oracle oinstall 1024 Aug 17 16:53 1_45_791488634.dbf

-rw-r----- 1 oracle oinstall 1024 Aug 17 16:53 1_42_791488634.dbf

-rw-r----- 1 oracle oinstall 1024 Aug 17 16:53 1_43_791488634.dbf

-rw-r----- 1 oracle oinstall 32768 Aug 17 16:53 1_41_791488634.dbf

5 执行backup database plus archivelog delete all input ;查看日志发现oracle删除的归档日志 发现删除了两个目录下的所有日志

archived log file name=/oracle/archive2/1_40_791488634.dbf RECID=335 STAMP=791570187

archived log file name=/oracle/archive/1_41_791488634.dbf RECID=336 STAMP=791571221

archived log file name=/oracle/archive2/1_41_791488634.dbf RECID=337 STAMP=791571221

archived log file name=/oracle/archive2/1_42_791488634.dbf RECID=339 STAMP=791571222

archived log file name=/oracle/archive/1_42_791488634.dbf RECID=338 STAMP=791571222

archived log file name=/oracle/archive/1_43_791488634.dbf RECID=340 STAMP=791571222

archived log file name=/oracle/archive2/1_43_791488634.dbf RECID=341 STAMP=791571222

archived log file name=/oracle/archive/1_44_791488634.dbf RECID=342 STAMP=791571228

archived log file name=/oracle/archive2/1_44_791488634.dbf RECID=343 STAMP=791571228

archived log file name=/oracle/archive/1_45_791488634.dbf RECID=344 STAMP=791571228

archived log file name=/oracle/archive2/1_45_791488634.dbf RECID=345 STAMP=791571228

archived log file name=/oracle/archive2/1_46_791488634.dbf RECID=347 STAMP=791571229

archived log file name=/oracle/archive/1_46_791488634.dbf RECID=346 STAMP=791571229

archived log file name=/oracle/archive/1_47_791488634.dbf RECID=348 STAMP=791571526

archived log file name=/oracle/archive2/1_47_791488634.dbf RECID=349 STAMP=791571526

oracle官方文档提示：

If you had specified DELETE INPUT rather than DELETE ALL INPUT, then RMAN would have only deleted the specific archived redo log files that it backed up. For

example, RMAN would delete the logs in /arc_dest1 if these files were used as the source of the backup, but leave the contents of the /arc_dest2 intact

如果指定delete input ，则rman将仅删除已备份的归档日志，例如对于有两个归档目录 /arc_dest1 和 /arc_dest2 如果把/arc_dest1做为backup的源，delete input将删除 /arc_dest1 中的内容，保留dest2中的归档日志，通过实验可以看出，就算有两个归档位置，rman备份的时候貌似是以日志文件为源，备份过的日志删除。

If you had specified DELETE ALL INPUT RMAN backs up only one copy of each log sequence number in these archiving locations.it deletes all copies of any log that it backed up from the other archiving destinations

如果指定delete all input RMAN对于所有归档目录中的日志序列号只备份一次，同时rman会删除dest1 和dest2 中所有归档日志。

结论：对于仅有一个归档目录 delete input 和delet all input 没啥区别。

六 rman备份恢复对归档位置的说明

注意：

1 当需要执行恢复时，rman会自动从备份恢复归档日志，如果归档备份的的话。

2 参数 log_archive_format 和 log_archive_dest_n 决定了rman恢复时获取归档的路径和名字。

eg

SQL> show parameter log_archive_dest_1;

NAME TYPE VALUE

log_archive_dest_1 string LOCATION=/oracle/archive

SQL> show parameter log_archive_format;

NAME TYPE VALUE

log_archiveformat string %t%s_%r.dbf

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 13 - see DBWR trace file

ORA-01110: data file 13: '/oracle/test/test2.dbf'

SQL> select * from v$recoverfile;

FILE# ONLINE ONLINE ERROR CHANGE# TIME

13 ONLINE ONLINE FILE NOT FOUND 0

[oracle@oracle ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sun May 18 13:10:18 2014

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

connected to target database: CRM (DBID=3601019238, not open)

RMAN> run{

2> restore datafile 13;

3> recover datafile 13;

4> sql'alter database open';

5> }

Starting restore at 18-MAY-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=85 device type=DISK

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 00013 to /oracle/test/test2.dbf

channel ORA_DISK_1: reading from backup piece /backup/fullbk_20140518_09p8jeov_1_1.bk

channel ORA_DISK_1: piece handle=/backup/fullbk_20140518_09p8jeov_1_1.bk tag=TAG20140518T121214

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 18-MAY-14

Starting recover at 18-MAY-14

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 80 is already on disk as file /oracle/archive/1_80_839187351.dbf

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=64

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=65

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=66

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=67

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=68

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=69

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=70

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=71

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=72

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=73

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=74

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=75

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=76

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=77

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=78

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=79

channel ORA_DISK_1: reading from backup piece /backup/archbk_20140518_0cp8jg8f_1_1.bak

channel ORA_DISK_1: piece handle=/backup/archbk_20140518_0cp8jg8f_1_1.bak tag=TAG20140518T123735

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/oracle/archive/1_64_839187351.dbf thread=1 sequence=64

archived log file name=/oracle/archive/1_65_839187351.dbf thread=1 sequence=65

archived log file name=/oracle/archive/1_66_839187351.dbf thread=1 sequence=66

archived log file name=/oracle/archive/1_67_839187351.dbf thread=1 sequence=67

archived log file name=/oracle/archive/1_68_839187351.dbf thread=1 sequence=68

archived log file name=/oracle/archive/1_69_839187351.dbf thread=1 sequence=69

archived log file name=/oracle/archive/1_70_839187351.dbf thread=1 sequence=70

archived log file name=/oracle/archive/1_71_839187351.dbf thread=1 sequence=71

archived log file name=/oracle/archive/1_72_839187351.dbf thread=1 sequence=72

archived log file name=/oracle/archive/1_73_839187351.dbf thread=1 sequence=73

archived log file name=/oracle/archive/1_74_839187351.dbf thread=1 sequence=74

archived log file name=/oracle/archive/1_75_839187351.dbf thread=1 sequence=75

media recovery complete, elapsed time: 00:00:01

Finished recover at 18-MAY-14

sql statement: alter database open

RMAN> exit

Recovery Manager complete.

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun May 18 13:13:11 2014

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$recover_file;

no rows selected

SQL> select open_mode from v$database;

OPEN_MODE

READ WRITE

1 从归档备份恢复归档到新的位置

run{

set archivelog destination to '/oracle/archive/test/';

restore archivelog from sequence 64;

}

eg

RMAN> run{

2> set archivelog destination to '/oracle/archive/test/';

3> restore archivelog from sequence 64;

4> }

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 18-MAY-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log restore to user-specified destination

archived log destination=/oracle/archive/test/

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=64

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=65

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=66

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=67

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=68

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=69

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=70

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=71

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=72

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=73

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=74

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=75

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=76

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=77

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=78

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=79

channel ORA_DISK_1: reading from backup piece /backup/archbk_20140518_0cp8jg8f_1_1.bak

channel ORA_DISK_1: piece handle=/backup/archbk_20140518_0cp8jg8f_1_1.bak tag=TAG20140518T123735

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

Finished restore at 18-MAY-14

2 调用新位置的归档进行数据库恢复

run{

set archivelog destination to '/oracle/archive/test/';

restore datafile 13;

recover datafile 13;

sql'alter database open';

}

eg

SQL> startup

ORACLE instance started.

Total System Global Area 1043886080 bytes

Fixed Size 2233088 bytes

Variable Size 327159040 bytes

Database Buffers 708837376 bytes

Redo Buffers 5656576 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 13 - see DBWR trace file

ORA-01110: data file 13: '/oracle/test/test2.dbf'

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

13 ONLINE ONLINE FILE NOT FOUND 0

QL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@oracle ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sun May 18 12:56:03 2014

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

connected to target database: CRM (DBID=3601019238, not open)

RMAN> run{

2> set archivelog destination to '/oracle/archive/test/';

3> restore datafile 13;

4> recover datafile 13;

5> sql'alter database open';

6> }

executing command: SET ARCHIVELOG DESTINATION

using target database control file instead of recovery catalog

Starting restore at 18-MAY-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=85 device type=DISK

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 00013 to /oracle/test/test2.dbf

channel ORA_DISK_1: reading from backup piece /backup/fullbk_20140518_09p8jeov_1_1.bk

channel ORA_DISK_1: piece handle=/backup/fullbk_20140518_09p8jeov_1_1.bk tag=TAG20140518T121214

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 18-MAY-14

Starting recover at 18-MAY-14

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 64 is already on disk as file /oracle/archive/test/1_64_839187351.dbf

archived log for thread 1 with sequence 65 is already on disk as file /oracle/archive/test/1_65_839187351.dbf

archived log for thread 1 with sequence 66 is already on disk as file /oracle/archive/test/1_66_839187351.dbf

archived log for thread 1 with sequence 67 is already on disk as file /oracle/archive/test/1_67_839187351.dbf

archived log for thread 1 with sequence 68 is already on disk as file /oracle/archive/test/1_68_839187351.dbf

archived log for thread 1 with sequence 69 is already on disk as file /oracle/archive/test/1_69_839187351.dbf

archived log for thread 1 with sequence 70 is already on disk as file /oracle/archive/test/1_70_839187351.dbf

archived log for thread 1 with sequence 71 is already on disk as file /oracle/archive/test/1_71_839187351.dbf

archived log for thread 1 with sequence 72 is already on disk as file /oracle/archive/test/1_72_839187351.dbf

archived log for thread 1 with sequence 73 is already on disk as file /oracle/archive/test/1_73_839187351.dbf

archived log for thread 1 with sequence 74 is already on disk as file /oracle/archive/test/1_74_839187351.dbf

archived log for thread 1 with sequence 75 is already on disk as file /oracle/archive/test/1_75_839187351.dbf

archived log for thread 1 with sequence 76 is already on disk as file /oracle/archive/test/1_76_839187351.dbf

archived log for thread 1 with sequence 77 is already on disk as file /oracle/archive/test/1_77_839187351.dbf

archived log for thread 1 with sequence 78 is already on disk as file /oracle/archive/test/1_78_839187351.dbf

archived log for thread 1 with sequence 79 is already on disk as file /oracle/archive/test/1_79_839187351.dbf

archived log file name=/oracle/archive/test/1_64_839187351.dbf thread=1 sequence=64

archived log file name=/oracle/archive/test/1_65_839187351.dbf thread=1 sequence=65

archived log file name=/oracle/archive/test/1_66_839187351.dbf thread=1 sequence=66

archived log file name=/oracle/archive/test/1_67_839187351.dbf thread=1 sequence=67

archived log file name=/oracle/archive/test/1_68_839187351.dbf thread=1 sequence=68

archived log file name=/oracle/archive/test/1_69_839187351.dbf thread=1 sequence=69

archived log file name=/oracle/archive/test/1_70_839187351.dbf thread=1 sequence=70

archived log file name=/oracle/archive/test/1_71_839187351.dbf thread=1 sequence=71

archived log file name=/oracle/archive/test/1_72_839187351.dbf thread=1 sequence=72

archived log file name=/oracle/archive/test/1_73_839187351.dbf thread=1 sequence=73

archived log file name=/oracle/archive/test/1_74_839187351.dbf thread=1 sequence=74

media recovery complete, elapsed time: 00:00:01

Finished recover at 18-MAY-14

sql statement: alter database open

RMAN> exit

Recovery Manager complete.

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun May 18 12:57:06 2014

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$recover_file;

no rows selected

八、 删除归档

1 如何正确的删除归档

删除数据库归档前，应该对数据库做一次全备份，为何这样说呢，举个简单的例子 当前oracle有6组日志组，seq号分别是 33、34、35、36、37、38，当前日志组seq号是38，

归档日志seq号从1到37，此时我们对数据库做全备份，当数据库需要进行恢复时，restore后数据文件头部的rba seq号为38，之后执行recover这个动作数据库会从seq号为38号的归档或者联机日志开始。seq号为38之前的归档，恢复时已经不需要了。这也就是我们为啥说对数据库做一次全备份后

即可删除所有归档的原因。

2 如何正确的在操作系统上删除归档

需要明确的一点就是，通过操作系统命令删除归档，rman资料库里面记录该归档的状态为A即为avaliable，这样就造成了rman资料库归档条目状态不一致性，因此当

我们在操作系统上删除了归档后，还应该发起一个crosscheck archivelog all 命令，用于校验rman资料库中的每一条归档记录在数据库上是否存在，如果不存在则更改

改归档条目的记录状态为expired。之后我们便可用命令delete noprompt expired archivelog all 删除rman资料库中所有标记为EXPIRED状态的条目。

eg 命令如下：

rm -rf ×

rman target /

crosscheck archivelog all;

delete noprompt expired archivelog all;

3 在rman下如何删除归档

删除系统内的所有归档:delete noprompt archivelog all

删除7天内以外的所有归档：delete noprompt archivelog all completed before 'SYSDATE - 7';