段亚东的Oracle笔记

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

Oracle归档日志默认路径为：USR_DB_RECOVERY_FILE_DEST 也就是闪回恢复区($ORACLE_BASE/flash_recovery_area),默认情况下ORACLE对这个路径有限制，且这个路径也是默认备份文件和闪回日志的存储路径，所以尽量去修改一下。

SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence       9
SQL> show parameter db_recovery     -----查看路径以及大小

NAME         TYPE  VALUE
------------------------------------      -----------   ------------------------------
db_recovery_file_dest     string   /u01/app/oracle/fast_recovery_ area
db_recovery_file_dest_size     big integer  3000M

SQL> select name,space_limit,space_used from v$recovery_file_dest;   -----查看路径以及大小

NAME   SPACE_LIMIT      SPACE_USED
--------------------------------------------------------- ------------------     --------------------
/u01/app/oracle/fast_recovery_area 3145728000      491435520

Elapsed: 00:00:00.01
SQL>

如果闪回区太小可以使用以下命令修改
SQL> alter system set db_recovery_file_dest_size=500M;     ----修改为500M；

System altered.

Elapsed: 00:00:00.00
SQL> show parameter db_recovery

NAME     TYPE        VALUE
------------------------------------ ------------------      ------------------------------
db_recovery_file_dest     string    /u01/app/oracle/fast_recovery_ area
db_recovery_file_dest_size     big integer   500M
SQL> select name,space_limit,space_used from v$recovery_file_dest;

NAME     SPACE_LIMIT    SPACE_USED
------------------------------------------------------------ -------------------   ----------
/u01/app/oracle/fast_recovery_area  524288000     491435520

Elapsed: 00:00:00.00
SQL>

----------------------------------------------------------------------------------------------------------------------------
修改归档日志存放路径
SQL> alter system set log_archive_dest_1='location=/home/oracle/arc_log';

System altered.

SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       /home/oracle/arc_log
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence       9
SQL> alter system switch logfile;

System altered.

SQL> !ls /home/oracle/arc_log
1_9_899418370.dbf

SQL> alter system switch logfile;

System altered.

SQL> !ls /home/oracle/arc_log
1_10_899418370.dbf  1_9_899418370.dbf

SQL>

----------------------------------------------------------------------------------------------------------------------
归档命名
SQL> show parameter log_archive_format

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format     string %t_%s_%r.dbf

注释： %t  线程号，可以理解为节点号，非RAC环境均为1
%s 日志切换号，也就是archive log list 中的当前日志序列
%r 场景号
%d DBID,16进制标识的DBID
修改归档文件命名格式
SQL> alter system set log_archive_format='ARC_LOG_%T_%S_%R_%D.dbf' scope=spfile;

System altered.
SQL> alter system set log_archive_dest_1='location=/home/oracle/arc_log' scope=spfile;

System altered.

SQL> startup
ORACLE instance started.

Total System Global Area  776646656 bytes
Fixed Size    2257272 bytes
Variable Size  511708808 bytes
Database Buffers  255852544 bytes
Redo Buffers    6828032 bytes
Database mounted.
Database opened.
SQL> show parameter log_archive_dest_1

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1     string location=/home/oracle/arc_log
log_archive_dest_10     string
log_archive_dest_11     string
log_archive_dest_12     string
log_archive_dest_13     string
log_archive_dest_14     string
log_archive_dest_15     string
log_archive_dest_16     string
log_archive_dest_17     string
log_archive_dest_18     string
log_archive_dest_19     string
SQL> show parameter log_archive_format

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format     string ARC_LOG_%T_%S_%R_%D.dbf
SQL> !ls -l /home/oracle/arc_log
total 10444
-rw-r----- 1 oracle oinstall     1536 May 19 16:38 1_10_899418370.dbf
-rw-r----- 1 oracle oinstall 10688000 May 19 16:38 1_9_899418370.dbf

SQL> alter system switch logfile;

System altered.

SQL> !ls -l /home/oracle/arc_log
total 12788
-rw-r----- 1 oracle oinstall     1536 May 19 16:38 1_10_899418370.dbf
-rw-r----- 1 oracle oinstall 10688000 May 19 16:38 1_9_899418370.dbf
-rw-r----- 1 oracle oinstall  2398720 May 19 17:16 ARC_LOG_0001_0000000011_0899418370_5b357dcf.dbf

SQL> alter system switch logfile;

System altered.

SQL> !ls -l /home/oracle/arc_log
total 12804
-rw-r----- 1 oracle oinstall     1536 May 19 16:38 1_10_899418370.dbf
-rw-r----- 1 oracle oinstall 10688000 May 19 16:38 1_9_899418370.dbf
-rw-r----- 1 oracle oinstall  2398720 May 19 17:16 ARC_LOG_0001_0000000011_0899418370_5b357dcf.dbf
-rw-r----- 1 oracle oinstall    12800 May 19 17:18 ARC_LOG_0001_0000000012_0899418370_5b357dcf.dbf

SQL> alter system switch logfile;

System altered.

SQL> !ls -l /home/oracle/arc_log
total 12808
-rw-r----- 1 oracle oinstall     1536 May 19 16:38 1_10_899418370.dbf
-rw-r----- 1 oracle oinstall 10688000 May 19 16:38 1_9_899418370.dbf
-rw-r----- 1 oracle oinstall  2398720 May 19 17:16 ARC_LOG_0001_0000000011_0899418370_5b357dcf.dbf
-rw-r----- 1 oracle oinstall    12800 May 19 17:18 ARC_LOG_0001_0000000012_0899418370_5b357dcf.dbf
-rw-r----- 1 oracle oinstall     3072 May 19 17:18 ARC_LOG_0001_0000000013_0899418370_5b357dcf.dbf

SQL>