archivelog启动关闭与日常维护

最新推荐文章于 2021-04-02 19:56:45 发布

AfraidORA_600

最新推荐文章于 2021-04-02 19:56:45 发布

阅读量452

点赞数

文章标签：技术备份

本文链接：https://blog.csdn.net/AfraidORA_600/article/details/48291003

版权

Oracle数据库可以运行在2种模式下:归档模式(archivelog)和非归档模式(noarchivelog)
归档模式可以提高Oracle数据库的可恢复性，生产数据库都应该运行在此模式下，归档模式应该和相应的备份策略相结合，只有归档模式没有相应的备份策略只会带来麻烦。

本文简单介绍如何启用和关闭数据库的归档模式。

1.shutdown normal或shutdown immediate关闭数据库

[oracle@jumper oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Oct 15 15:48:36 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

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

2.启动数据库到mount状态

SQL> startup mount;
ORACLE instance started.

Total System Global Area  101782828 bytes
Fixed Size                   451884 bytes
Variable Size              37748736 bytes
Database Buffers           62914560 bytes
Redo Buffers                 667648 bytes
Database mounted.

3.启用或停止归档模式
如果要启用归档模式，此处使用
alter database archivelog 命令。

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            /opt/oracle/oradata/conner/archive
Oldest online log sequence     148
Next log sequence to archive   151
Current log sequence           151

如果需要停止归档模式，此处使用：
alter database noarchivelog 命令。

SQL> shutdown immediate; 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  101782828 bytes
Fixed Size                   451884 bytes
Variable Size              37748736 bytes
Database Buffers           62914560 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/oradata/conner/archive
Oldest online log sequence     149
Current log sequence           152

如果在启动过程中遇到ORA-00257: archiver error. Connect internal only,until freed 错误的处理方法

1. 用sys用户登录

sqlplus sys/pass@tt as sysdba

2. 看看archiv log所在位置

SQL> show parameter log_archive_dest;

NAME TYPE VALUE

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

log_archive_dest string

log_archive_dest_1 string

log_archive_dest_10 string

3. 一般VALUE为空时，可以用archivelog list;检查一下归档目录和logsequence

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 360

Next log sequence to archive 360

Current log sequence 362

4. 检查flash recovery area的使用情况,可以看见archivelog已经很大了，达到96.62

SQL> select * fromV$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

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

CONTROLFILE .13 0 1

ONLINELOG 2.93 0 3

ARCHIVELOG 96.62 0 141

BACKUPPIECE 0 0 0

IMAGECOPY 0 0 0

FLASHBACKLOG 0 0 0

5. 计算flash recovery area已经占用的空间

SQL> selectsum(percent_space_used)*3/100 from v$flash_recovery_area_usage;

SUM(PERCENT_SPACE_USED)*3/100

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

2.9904

6. 找到recovery目录, show parameter recover

SQL> show parameter recover;

NAME TYPE VALUE

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

db_recovery_file_dest string /u01/app/oracle/flash_recovery_area

db_recovery_file_dest_size big integer 5G

recovery_parallelism integer 0

7 上述结果告诉我们，归档位置用的是默认值，放在flash_recovery_area下(db_recovery_file_dest目录=/u01/app/oracle/flash_recovery_area)

[root@sha3 10.2.0]# echo $ORACLE_BASE

/u01/app/oracle

[root@sha3 10.2.0]# cd$ORACLE_BASE/flash_recovery_area/tt/archivelog

转移或清除对应的归档日志, 删除一些不用的日期目录的文件，注意保留最后几个文件（比如360以后的）

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

注意:

在删除归档日志后，必须用RMAN维护控制文件，否则空间显示仍然不释放。

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

8. rman target sys/pass

[root@sha3 oracle]# rman target sys/pass

Recovery Manager: Release 10.2.0.4.0 -Production on Tue Jan 20 01:41:26 2009

connected to target database: tt(DBID=4147983671)

9. 检查一些无用的archivelog

RMAN> crosscheck archivelog all;

10. 删除过期的归档

RMAN> delete expired archivelog all;

delete archivelog until time 'sysdate-1' ; 删除截止到前一天的所有archivelog

11. 再次查询，发现使用率正常，已经降到23.03

SQL> select * fromV$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

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

CONTROLFILE .13 0 1

ONLINELOG 2.93 0 3

ARCHIVELOG 23.03 0 36

BACKUPPIECE 0 0 0

IMAGECOPY 0 0 0

FLASHBACKLOG 0 0 0

其它有用的Command:

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

如果archive log模式下不能正常startup,则先恢复成noarchive log,startup成功后，再shutdown;

shutdown immediate;

startup mount;

alter database noarchivelog;

alter database open;

shutdown immediate;

再次startup以archive log模式

shutdown immediate;

startup mount;

show parameter log_archive_dest;

alter database archivelog;

archive log list;

alter database open;

如果还不行，则删除一些archloglog

SQL> select group#,sequence# from v$log;

GROUP# SEQUENCE#

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

1 62

3 64

2 63

原来是日志组一的一个日志不能归档

SQL> alter database clear unarchivedlogfile group 1;

alter database open;

最后，也可以指定位置ArchLog, 请按照如下配置

select name from v$datafile;

alter system setlog_archive_dest='/opt/app/oracle/oradata/usagedb/arch' scope=spfile

或者修改大小

SQL> alter system setdb_recovery_file_dest_size=3G scope=both;

AfraidORA_600

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
archivelog启动关闭与日常维护

Oracle数据库可以运行在2种模式下:归档模式(archivelog)和非归档模式(noarchivelog)归档模式可以提高Oracle数据库的可恢复性，生产数据库都应该运行在此模式下，归档模式应该和相应的备份策略相结合，只有归档模式没有相应的备份策略只会带来麻烦。本文简单介绍如何启用和关闭数据库的归档模式。 1.shutdown normal或shutdown i
复制链接

扫一扫