Oracle数据库归档模式配置及日志存放位置变更-CSDN博客

Oracle开启归档模式

1. 参考资料：

Oracle开启归档模式
http://guojuanjun.blog.51cto.com/277646/295454

还有一种说法：

数据库启用归档详细步骤
1)查看是否有该实例对应的pfile文件，如果没有就通过spfile生成pfile文件：
create pfile='/oracle/product/10.2.0/db/dbs/inittelfree.ora' from spfile

2）修改pfile文件中的下面3个参数
*.log_archive_max_processes=3
*.log_archive_dest_1='LOCATION=/oracle/archive'
*.log_archive_format='arch_%t_%s_%r.arc'

3）关闭数据库
SQL>shutdown immediate;

4）用pfile启动到mount状态
startup mount pfile='/oracle/product/10.2.0/db/dbs/inittelfree.ora'

5）启用archivelog
SQL> alter database archivelog;

6）打开数据库，查看状态：
SQL> alter database open;

7）查看确认
SQL> archive log list;

8）重建SPFILE
create spfile from pfile='/oracle/product/10.2.0/db/dbs/inittelfree.ora'

9）测试，查看是否产生归档文件
SQL> alter system switch logfile;

2.实施

（也许是我下错oracle安装文件了，安装的是英文版的oracle。还好没有装错字符集。）

/*--设置ORACLE_SID--*/

[oracle@QL DMP]$ export ORACLE_SID=orcl
[oracle@QL DMP]$ echo $ORACLE_SID
orcl

/*--登录oracle--*/

[oracle@QL DMP]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 30 10:14:29 2013

SQL> connect sys as sysdba;
Enter password:
Connected.

/*--查看是否开启归档，默认安装是不开启归档的--*/

SQL> archive log list
Database log mode        No Archive Mode
Automatic archival        Disabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     56
Current log sequence        58

/*--开归档是需要在mount模式下操作，为此必须先关闭数据库--*/

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

/*--启动到mount模式下--*/

SQL> startup mount
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size      2217952 bytes
Variable Size    641730592 bytes
Database Buffers   184549376 bytes
Redo Buffers      6606848 bytes
Database mounted.

/*--开启归档--*/

SQL> alter database archivelog;

Database altered.

/*--查看归档模式是否开启--*/

SQL> archive log list
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     56
Next log sequence to archive   58
Current log sequence        58

/*--切换数据库至open，开启后数据库就可以正常运行--*/

SQL> alter database open ;

Database altered.

/*--查询归档情况--*/

SQL> select * from v$archived_log ;

no rows selected

——没有记录

/*--测试归档--*/

SQL> alter system switch logfile;

System altered.

/*--再次查看归档情况，应该是成功了--*/

SQL> select * from v$archived_log;

     RECID STAMP
---------- ----------
NAME
--------------------------------------------------------------------------------
   DEST_ID    THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS RESETLOGS_ID
---------- ---------- ---------- ----------------- --------- ------------
FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME    BLOCKS BLOCK_SIZE CREATOR
------------- --------- ------------ --------- ---------- ---------- -------
REGISTR STA ARC APPLIED   DEL S COMPLETIO DIC DIC END BACKUP_COUNT
------- --- --- --------- --- - --------- --- --- --- ------------
ARCHIVAL_THREAD# ACTIVATION# IS_ COM FAL END_OF_RED BAC
---------------- ----------- --- --- --- ---------- ---
1 806062742

3. 变更归档日志存放位置

3.1 创建目录archival
[root@QL oracle]# mkdir -p /qlone/oracle/archival
[root@QL oracle]# chown -R oracle:dba /qlone/oracle/archival/
[root@QL oracle]# chmod -R 775 /qlone/oracle/archival/

3.2 切换日志存放路径

/*--关闭数据库--*/

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

/*--启动数据库至mount状态--*/
SQL> startup mount
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size      2217952 bytes
Variable Size    641730592 bytes
Database Buffers   184549376 bytes
Redo Buffers      6606848 bytes
Database mounted.

/*--查看日志情况--*/
SQL> archive log list;
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     57
Next log sequence to archive   59
Current log sequence        59

/*--清除原日志路径--*/
SQL> alter system set db_recovery_file_dest='';

System altered.

/*--修改日志路径为'/qlone/oracle/archival'--*/

SQL> alter system set db_recovery_file_dest='/qlone/oracle/archival';

System altered.

//网上的资料显示，修改路径还有带个参数。

更改归档路径：
SQL> alter system set log_archive_dest_1='location=E:\archive' scope=spfile;

系统已更改。

/*--切换数据库至open--*/

SQL> alter database open;

Database altered.

/*--测试归档--*/

SQL> alter system switch logfile;

System altered.

/*--查看归档情况--*/
select * from v$archived_log

STAMP NAME

806062742 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_30/o1_mf_1_58_8jk0romo_.arc
806064284 /qlone/oracle/archival/ORCL/archivelog/2013_01_30/o1_mf_1_59_8jk28w1w_.arc

——看上去切换成功了。测试暂告结束。

回去还得找些文档认真看看。