[20130123]归档参数log_archive_dest_1问题.txt

[20130123]归档参数log_archive_dest_1问题.txt

SQL> select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------------------
log_archive_dest_1                   string      LOCATION=/u01/app/oracle11g/archivelog

--如果归档目录不存在会有什么问题呢？

$ mv /u01/app/oracle11g/archivelog /u01/app/oracle11g/archivelog1
$ ll  -d /u01/app/oracle11g/archi*
drwxr-xr-x  2 oracle11g oinstall 4096 Jan 23 08:38 /u01/app/oracle11g/archivelog1

--可以发现我已经修改了目录。关闭数据库重新启动。

SQL> startup

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2213776 bytes

Variable Size            1258293360 bytes

Database Buffers          335544320 bytes

Redo Buffers                7360512 bytes

Database mounted.

Database opened.

SQL> archive log list ;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle11g/archivelog

Oldest online log sequence     121

Next log sequence to archive   123

Current log sequence           123

SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------------------------------------

log_archive_dest_1                   string      LOCATION=/u01/app/oracle11g/archivelog

SQL> alter system archive log current ;
System altered.

SQL> alter system archive log current ;
System altered.

SQL> alter system archive log current ;
System altered.

SQL> alter system archive log current ;
System altered.

$ ll  -d /u01/app/oracle11g/archi*

drwxr-xr-x  2 oracle11g oinstall     4096 Jan 23 08:38 /u01/app/oracle11g/archivelog1

-rw-r-----  1 oracle11g oinstall 15728128 Jan 23 15:21 /u01/app/oracle11g/archivelog1_123_798551880.dbf

-rw-r-----  1 oracle11g oinstall     1024 Jan 23 15:21 /u01/app/oracle11g/archivelog1_124_798551880.dbf

-rw-r-----  1 oracle11g oinstall     2048 Jan 23 15:21 /u01/app/oracle11g/archivelog1_125_798551880.dbf

-rw-r-----  1 oracle11g oinstall     1024 Jan 23 15:21 /u01/app/oracle11g/archivelog1_126_798551880.dbf

--可以发现归档放在了/u01/app/oracle11g目录，并且以archivelog开头。
SQL> select  name from v$archived_log where sequence#>=123;

NAME

--------------------------------------------------

/u01/app/oracle11g/archivelog1_123_798551880.dbf

/u01/app/oracle11g/archivelog1_124_798551880.dbf

/u01/app/oracle11g/archivelog1_125_798551880.dbf

/u01/app/oracle11g/archivelog1_126_798551880.dbf

--从视图显示也可以确定情况确实向上面一样。
--当然这并不影响使用，只不过不好。

2.再做一些补充:
--如果执行如下：(注意后面有一个/)
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle11g/archivelog/';
alter system set log_archive_dest_1='LOCATION=/u01/app/oracle11g/archivelog/'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
Linux-x86_64 Error: 2: No such file or directory

--可以发现目录/u01/app/oracle11g/archivelog/不存在，报错。
--执行如下，OK！

SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle11g/archivelog1/';
System altered.

SQL> show parameter log_archive_dest_1
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------------------
log_archive_dest_1                   string      LOCATION=/u01/app/oracle11g/archivelog1/

SQL> alter system archive log current ;
System altered.

$ ll   -R /u01/app/oracle11g/archiv*

-rw-r-----  1 oracle11g oinstall 15728128 Jan 23 15:21 /u01/app/oracle11g/archivelog1_123_798551880.dbf

-rw-r-----  1 oracle11g oinstall     1024 Jan 23 15:21 /u01/app/oracle11g/archivelog1_124_798551880.dbf

-rw-r-----  1 oracle11g oinstall     2048 Jan 23 15:21 /u01/app/oracle11g/archivelog1_125_798551880.dbf

-rw-r-----  1 oracle11g oinstall     1024 Jan 23 15:21 /u01/app/oracle11g/archivelog1_126_798551880.dbf

/u01/app/oracle11g/archivelog1:

total 40

-rw-r-----  1 oracle11g oinstall 40960 Jan 23 15:29 1_127_798551880.dbf

--确实这样。
$ mv archivelog1 archivelog

SQL> alter system archive log current ;
alter system archive log current
*
ERROR at line 1:
ORA-16038: log 2 sequence# 128 cannot be archived
ORA-00312: online log 2 thread 1: '/u01/app/oracle11g/oradata/test/redo02.log'
ORA-07286: sksagdi: cannot obtain device information.
Linux-x86_64 Error: 2: No such file or directory

--这时log_archive_dest_1='LOCATION=/u01/app/oracle11g/archivelog1/',转换无法实现。
--这也是我今天帮别人解决的问题。
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle11g/archivelog';
System altered.

SQL> alter system archive log current ;
System altered.

--正常！

3.如果想把归档移动到/u01/app/oracle11g/archivelog目录，仅仅需要rman下catalog就可以了。
$ mv archivelog1* archivelog

RMAN> crosscheck copy ;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
validation failed for archived log
archived log file name=/u01/app/oracle11g/archivelog1_123_798551880.dbf RECID=61 STAMP=805476069
validation failed for archived log
archived log file name=/u01/app/oracle11g/archivelog1_124_798551880.dbf RECID=62 STAMP=805476070
validation failed for archived log
archived log file name=/u01/app/oracle11g/archivelog1_125_798551880.dbf RECID=63 STAMP=805476072
validation failed for archived log
archived log file name=/u01/app/oracle11g/archivelog1_126_798551880.dbf RECID=64 STAMP=805476073
validation failed for archived log
archived log file name=/u01/app/oracle11g/archivelog1/1_127_798551880.dbf RECID=65 STAMP=805476599
validation succeeded for archived log
archived log file name=/u01/app/oracle11g/archivelog/1_128_798551880.dbf RECID=66 STAMP=805476917
validation succeeded for archived log
archived log file name=/u01/app/oracle11g/archivelog/1_129_798551880.dbf RECID=67 STAMP=805476917
Crosschecked 7 objects


RMAN> delete expired  archivelog all ;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
List of Archived Log Copies for database with db_unique_name TEST
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
61      1    123     X 2013-01-23 08:36:17
        Name: /u01/app/oracle11g/archivelog1_123_798551880.dbf

62      1    124     X 2013-01-23 15:21:08
        Name: /u01/app/oracle11g/archivelog1_124_798551880.dbf

63      1    125     X 2013-01-23 15:21:09
        Name: /u01/app/oracle11g/archivelog1_125_798551880.dbf

64      1    126     X 2013-01-23 15:21:12
        Name: /u01/app/oracle11g/archivelog1_126_798551880.dbf

65      1    127     X 2013-01-23 15:21:13
        Name: /u01/app/oracle11g/archivelog1/1_127_798551880.dbf


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/u01/app/oracle11g/archivelog1_123_798551880.dbf RECID=61 STAMP=805476069
deleted archived log
archived log file name=/u01/app/oracle11g/archivelog1_124_798551880.dbf RECID=62 STAMP=805476070
deleted archived log
archived log file name=/u01/app/oracle11g/archivelog1_125_798551880.dbf RECID=63 STAMP=805476072
deleted archived log
archived log file name=/u01/app/oracle11g/archivelog1_126_798551880.dbf RECID=64 STAMP=805476073
deleted archived log
archived log file name=/u01/app/oracle11g/archivelog1/1_127_798551880.dbf RECID=65 STAMP=805476599
Deleted 5 EXPIRED objects

RMAN> catalog start with '/u01/app/oracle11g/archivelog/';
searching for all files that match the pattern /u01/app/oracle11g/archivelog/

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle11g/archivelog/1_127_798551880.dbf
File Name: /u01/app/oracle11g/archivelog/archivelog1_126_798551880.dbf
File Name: /u01/app/oracle11g/archivelog/archivelog1_124_798551880.dbf
File Name: /u01/app/oracle11g/archivelog/archivelog1_125_798551880.dbf
File Name: /u01/app/oracle11g/archivelog/archivelog1_123_798551880.dbf

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle11g/archivelog/1_127_798551880.dbf
File Name: /u01/app/oracle11g/archivelog/archivelog1_126_798551880.dbf
File Name: /u01/app/oracle11g/archivelog/archivelog1_124_798551880.dbf
File Name: /u01/app/oracle11g/archivelog/archivelog1_125_798551880.dbf
File Name: /u01/app/oracle11g/archivelog/archivelog1_123_798551880.dbf