oracle 归档管理

2022-10-05 20:22:21

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

非归档，联机日志文件循环覆盖，数据库执行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‘;

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

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

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

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

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

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‘;

oracle 归档管理

码农公寓

LOG_MODE

相关文章