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
Copyright (c) 1982, 2009, Oracle. All rights reserved.
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
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
---------------- ----------- --- --- --- ---------- ---
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_30/o1_mf_1_58_8jk0ro
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
---------------- ----------- --- --- --- ---------- ---
mo_.arc
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 1 58 945184 28-JAN-13 805889633
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
---------------- ----------- --- --- --- ---------- ---
1761195 30-JAN-13 1778161 30-JAN-13 66088 512 ARCH
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
---------------- ----------- --- --- --- ---------- ---
ARCH NO YES NO NO A 30-JAN-13 NO NO NO 0
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 1333615839 YES NO NO NO
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
---------------- ----------- --- --- --- ---------- ---
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
——看上去切换成功了。测试暂告结束。
回去还得找些文档认真看看。