oracle 12C 更改归档模式、目录、日志大小以及涉及到RMAN-00554错误

最新推荐文章于 2022-08-09 21:59:07 发布

三朝看客

最新推荐文章于 2022-08-09 21:59:07 发布

阅读量1.4k

点赞数 1

分类专栏：数据库oracle篇文章标签： oracle 归档日志 rman

dangcafe

本文链接：https://blog.csdn.net/xaiodang/article/details/108731233

版权

数据库oracle篇专栏收录该内容

39 篇文章 4 订阅

订阅专栏

oracle数据库在开启归档日志模式后，会自动启动新的进程：归档器ARCn。默认情况下是开启4个进程，在实际应用中最多可以启动30个归档器进程。

[oracle@locahost archivelog]$ ps -ef | grep -i ora_
oracle   26567     1  0 08:48 ?        00:00:00 ora_arc0_orcl
oracle   26573     1  0 08:48 ?        00:00:00 ora_arc1_orcl
oracle   26575     1  0 08:48 ?        00:00:00 ora_arc2_orcl
oracle   26577     1  0 08:48 ?        00:00:00 ora_arc3_orcl

归档日志(Archive Log)是非活动的重做日志备份.通过使用归档日志,可以保留所有重做历史记录,当数据库处于ARCHIVELOG模式并进行日志切换式,
后台进程ARCH会将重做日志的内容保存到归档日志中.当数据库出现介质失败时,使用数据文件备份,归档日志和重做日志可以完全恢复数据库.

非归档模式切换归档模式

修改之前：
确认环境。查看数据库，是否开启归档： —如下显示的信息，前提是安装数据库的时候，没有选择开启闪回，也没有选择开启归档

SQL> archive log list
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     4
Current log sequence           6
 
SQL> select name,log_mode from v$database;
NAME      LOG_MODE
--------- ------------
ORCL      NOARCHIVELOG
 
SQL>  show parameter db_recovery
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest             string
db_recovery_file_dest_size         big integer 0

如下测试没有开启闪回的情况下数据库从非归档模式切换到归档模式的操作

SQL> shutdown immediate   
Database closed.
 
SQL> startup mount
ORACLE instance started.
 
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           /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6
 
SQL> select name from v$archived_log;
no rows selected
 
SQL> alter system archive log current;
System altered.
 
SQL> select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
/home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_6_947091054.dbf
 
SQL>  show parameter recover;                                          
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest             string
db_recovery_file_dest_size         big integer 0
db_unrecoverable_scn_tracking     boolean     TRUE
recovery_parallelism             integer     0
 
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
no rows selected

将log_archive_log设置为默认值时，归档目录将会变为?/dbs/arch。

SQL> alter system set log_archive_dest_1='';
System altered.
 
SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           ?/dbs/arch
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup mount
ORACLE instance started.

重启数据库到mount后，归档目录变回为原先默认的目录。

SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7

—设置归档最大开启进程个数

SQL> alter system set log_archive_max_processes =  5;
System altered.

—修改日志存放格式

SQL> alter system set log_archive_format = "archive_%t_%s_%r.log" scope=spfile;
System altered.
SQL> shutdown immediate
Database dismounted.
ORACLE instance shut down
 
SQL> startup
ORACLE instance started.
 
Total System Global Area  939495424 bytes
Fixed Size            2258840 bytes
Variable Size          637536360 bytes
Database Buffers      293601280 bytes
Redo Buffers            6098944 bytes
Database mounted.
Database opened.
 
SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7
 
SQL>  show parameter db_recovery
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest             string
db_recovery_file_dest_size         big integer 0
 
SQL> select name from v$archived_log; 
NAME
--------------------------------------------------------------------------------
/home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_6_947091054.dbf
 
SQL> alter system switch logfile; 
System altered.

SQL> select recid, name, first_time from v$archived_log;
NAME
--------------------------------------------------------------------------------
/home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_6_947091054.dbf
/home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/archarchive_1_7_94709105
4.log
 
SQL> select NAME,SPACE_LIMIT/1024/1024/1024,SPACE_USED/1024/1024/1024,NUMBER_OF_FILES from V$RECOVERY_FILE_DEST;
 
no rows selected
 
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO

开启数据库闪回设置闪回恢复区路径以及大小测试先关闭归档模式然后再开启归档后的默认路径为数据库的闪回路径

查看数据库，是否开启闪回
注意：开启闪回功能的话，需要在归档模式下，要不闪回没有意义！

但是，开启归档的话，不一定需要开启闪回功能！
如果开启了闪回，再开启归档模式，归档日志没有指定新路径的话，那么归档日志是默认保存在闪回区的数据库中显示为：USE_DB_RECOVERY_FILE_DEST，实际对应闪回的路径。

另外闪回区和归档路径的位置和大小都是可以手动设置的。

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO 
SQL> shutdown immediate
SQL> startup mount
 
SQL> alter database flashback on;    安装数据库的时候 没有选择开启闪回，出现了如下错误
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.
设置闪回恢复区
设置闪回区大小： oracle默认flash_recovery_area 为2G

SQL>  alter system set db_recovery_file_dest_size=5g scope=spfile;
查看闪回区指定路径的空间大小：

SQL> show parameter db_recovery_file_dest_size
 
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size	     big integer 30G
设置闪回区位置：

SQL> alter system set db_recovery_file_dest='/home/oracle/u01/app/oracle/account_flashback_area' scope=spfile;
设置闪回目标为5天，以分钟为单位，oracle默认1440分钟，即一天）

SQL> alter system set db_flashback_retention_target=7200 scope=spfile;
操作系统层面创建闪回相关目录：

[oracle@locahost oracle]$ mkdir -p /home/oracle/u01/app/oracle/account_flashback_area
 
SQL> alter database flashback on; 
Database altered.
SQL> alter database open;
Database altered.
 
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
 
SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8

结论：如果开启了闪回，再开启归档模式，归档日志没有指定新路径的话，那么归档日志是默认保存在闪回区的数据库中显示为：USE_DB_RECOVERY_FILE_DEST，实际对应闪回的路径。

目前我们再测试关闭闪回重启数据库查看归档日志路径

SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> shutdown immediate
Database closed.
SQL> startup mount
ORACLE instance started.

关闭归档模式 ----提示我们数据库运行在归档模式闪回开启的情况下，是不能直接关闭归档的

SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback database is enabled

那么我们就先关闭闪回

SQL> alter database flashback off;
Database altered.
 
SQL> alter database noarchivelog;
Database altered.
 
SQL> alter database open;
Database altered.
 
SQL> archive log list
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     6
Current log sequence           8
 
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
重新启动，开启归档 查看归档路径

SQL> shutdown immediate
Database closed.
SQL> startup mount    
ORACLE instance started.
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           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8
 
SQL> select flashback_on,open_mode from v$database;
FLASHBACK_ON       OPEN_MODE
------------------ --------------------
NO           READ WRITE

结论：开启归档的话，不一定需要开启闪回功能，可以不开启闪回！！！

测试3：修改归档默认路径

SQL> shutdown immediate
SQL> startup mount
ORACLE instance started.
 
SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8
 
SQL> alter system set log_archive_dest_1='location=/home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch';
System altered.
 
SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8
 
SQL> select flashback_on,open_mode from v$database;
 
FLASHBACK_ON       OPEN_MODE
------------------ --------------------
NO           MOUNTED

测试4：我们关闭归档先开启闪回再开启归档启动数据库查看归档路径是否会见自定义的路径修改为默认的闪回路径

SQL> alter database noarchivelog;
Database altered.
 
SQL> archive log list
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     6
Current log sequence           8
 
SQL> alter database flashback on;   ----非归档模式下 无法开启闪回  
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.

结论：非归档模式下无法开启闪回

SQL> alter database archivelog;
Database altered.
 
SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8
SQL> 
SQL> alter database flashback on;
Database altered.
 
SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8

结论：如果归档日志指定了存储路径那么开启闪回的话归档日志的存放路径还是保持用户原先指定的路径

继续：
修改归档日志为数据库默认的存放路径重复上述操作确认归档日志的路径是否为闪回区的路径

SQL> shutdown immediate
Database closed.
SQL> startup mount
ORACLE instance started.
 
SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8
 
SQL> alter system set log_archive_dest_1='';  ---修改归档日志为数据库默认的存放路径
System altered.
 
SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           ?/dbs/arch
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8
 
SQL> alter database open;
Database altered.
 
SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           ?/dbs/arch
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8
 
SQL> select flashback_on,open_mode from v$database;
FLASHBACK_ON       OPEN_MODE
------------------ --------------------
YES           READ WRITE
 
SQL> shutdown immediate
Database closed.
 
SQL> startup
ORACLE instance started.
 
SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8

结论：归档日志为数据库默认的存放路径，在闪回功能开启的情况下，切换为归档模式，归档日志的路径默认为闪回区的路径。

建catalog时，RMAN登陆target库时报错：

[oracle@liuqi1 ~]$ rman target system/oracle@test1 catalog
cata/cata@catadb Recovery Manager: Release 11.2.0.3.0 - Production on
Tue May 5 13:26:43 2015 Copyright © 1982, 2011, Oracle and/or its
affiliates. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database: ORA-01031: insufficient
privileges

解决方法：授权了sysdba的权限

[oracle@locahost~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue May 5 13:27:08 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant sysdba to system;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

三朝看客

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
打赏
0
评论
oracle 12C 更改归档模式、目录、日志大小以及涉及到RMAN-00554错误

oracle数据库在开启归档日志模式后，会自动启动新的进程：归档器ARCn。默认情况下是开启4个进程，在实际应用中最多可以启动30个归档器进程。[oracle@locahost archivelog]$ ps -ef | grep -i ora_oracle 26567 1 0 08:48 ? 00:00:00 ora_arc0_orcloracle 26573 1 0 08:48 ? 00:00:00 ora_arc1_orcloracle
复制链接

扫一扫