11g RAC和增加日志组大小和数量

这篇文章给大家介绍11g RAC和增加日志组大小和数量，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

备库关闭日志应用：

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

日志报错：

ORA-16037: user requested cancel of managed recovery operation

Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

Recovered data files to a consistent state at change 2463385381

Thu Oct 12 23:11:02 2017

MRP0: Background Media Recovery process shutdown (SCPRDDG)

Managed Standby Recovery Canceled (SCPRDDG)

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

主库：

SQL> select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log;

GROUP#    THREAD#    MEMBERS STATUS               SIZE_M

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

1          1          1 INACTIVE                 50

2          1          1 CURRENT                  50

3          2          1 INACTIVE                 50

4          2          1 CURRENT                  50

SQL> select group#,member from v$logfile order by 1;

GROUP#

----------

MEMBER

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

1

+DATA/scprd/onlinelog/group_1.262.929789943

2

+DATA/scprd/onlinelog/group_2.261.929789943

3

+DATA/scprd/onlinelog/group_3.258.929790053

GROUP#

----------

MEMBER

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

4

+DATA/scprd/onlinelog/group_4.257.929790053

5

+DATA/scprd/onlinelog/standby05.log

6

+DATA/scprd/onlinelog/standby06.log

GROUP#

----------

MEMBER

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

7

+DATA/scprd/onlinelog/standby07.log

8

+DATA/scprd/onlinelog/standby08.log

9

+DATA/scprd/onlinelog/standby09.log

GROUP#

----------

MEMBER

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

10

+DATA/scprd/onlinelog/standby10.log

10 rows selected.

SQL> alter database add logfile thread 1 group 11 '+DATA' size 250M;

Database altered.

SQL> select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log;

GROUP#    THREAD#    MEMBERS STATUS               SIZE_M

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

1          1          1 INACTIVE                 50

2          1          1 CURRENT                  50

3          2          1 INACTIVE                 50

4          2          1 CURRENT                  50

11          1          1 UNUSED                  250

SQL> alter database add logfile thread 2 group 15 '+DATA' size 250M;

Database altered.

SQL> select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log;

GROUP#    THREAD#    MEMBERS STATUS               SIZE_M

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

1          1          1 INACTIVE                 50

2          1          1 CURRENT                  50

3          2          1 INACTIVE                 50

4          2          1 CURRENT                  50

11          1          1 UNUSED                  250

12          1          1 UNUSED                  250

13          1          1 UNUSED                  250

14          1          1 UNUSED                  250

15          2          1 UNUSED                  250

9 rows selected.

SQL> select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log;

GROUP#    THREAD#    MEMBERS STATUS               SIZE_M

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

1          1          1 INACTIVE                 50

2          1          1 CURRENT                  50

3          2          1 INACTIVE                 50

4          2          1 CURRENT                  50

11          1          1 UNUSED                  250

12          1          1 UNUSED                  250

13          1          1 UNUSED                  250

14          1          1 UNUSED                  250

15          2          1 UNUSED                  250

16          2          1 UNUSED                  250

17          2          1 UNUSED                  250

GROUP#    THREAD#    MEMBERS STATUS               SIZE_M

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

18          2          1 UNUSED                  250

删除日志组

注：如果要删除的日志组的状态为current或active，让其变为inactive

alter system switch logfile;

alter system checkpoint;

SQL> alter system switch logfile;

System altered.

SQL> select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log;

GROUP#    THREAD#    MEMBERS STATUS               SIZE_M

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

1          1          1 INACTIVE                 50

2          1          1 ACTIVE                   50

3          2          1 INACTIVE                 50

4          2          1 CURRENT                  50

11          1          1 CURRENT                 250

12          1          1 UNUSED                  250

13          1          1 UNUSED                  250

14          1          1 UNUSED                  250

15          2          1 UNUSED                  250

16          2          1 UNUSED                  250

17          2          1 UNUSED                  250

GROUP#    THREAD#    MEMBERS STATUS               SIZE_M

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

18          2          1 UNUSED                  250

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log;

GROUP#    THREAD#    MEMBERS STATUS               SIZE_M

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

1          1          1 CURRENT                  50

4          2          1 ACTIVE                   50

11          1          1 ACTIVE                  250

12          1          1 ACTIVE                  250

13          1          1 ACTIVE                  250

14          1          1 ACTIVE                  250

15          2          1 CURRENT                 250

16          2          1 UNUSED                  250

17          2          1 UNUSED                  250

18          2          1 UNUSED                  250

把active变成inactive

SQL>  alter system checkpoint;

System altered.

SQL> /

GROUP#    THREAD#    MEMBERS STATUS               SIZE_M

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

4          2          1 INACTIVE                 50

11          1          1 INACTIVE                250

12          1          1 CURRENT                 250

13          1          1 INACTIVE                250

14          1          1 INACTIVE                250

15          2          1 CURRENT                 250

16          2          1 INACTIVE                250

17          2          1 INACTIVE                250

18          2          1 INACTIVE                250

9 rows selected.

SQL> alter database drop logfile group 4;

Database altered.

删除standby log

SQL> alter database drop standby logfile group 5;

Database altered.

SQL> alter database drop standby logfile group 7;

Database altered.

SQL> alter database drop standby logfile group 8;

Database altered.

SQL> alter database drop standby logfile group 9;

Database altered.

SQL> alter database drop standby logfile group 10;

Database altered.

SQL> alter database add standby logfile thread 1 group 1 '+DATA/scprd/onlinelog/standby01.log' size 250M;

Database altered.

SQL> alter database add standby logfile thread 1 group 2 '+DATA/scprd/onlinelog/standby02.log' size 250M;

Database altered.

SQL> alter database add standby logfile thread 1 group 3 '+DATA/scprd/onlinelog/standby03.log' size 250M;

Database altered.

SQL> alter database add standby logfile thread 1 group 4 '+DATA/scprd/onlinelog/standby04.log' size 250M;

Database altered.

SQL> alter database add standby logfile thread 2 group 10 '+DATA/scprd/onlinelog/standby10.log' size 250M ;

alter database add standby logfile thread 2 group 10 '+DATA/scprd/onlinelog/standby10.log' size 250M

*

ERROR at line 1:

ORA-00301: error in adding log file '+DATA/scprd/onlinelog/standby10.log' -

file cannot be created

ORA-17502: ksfdcre:4 Failed to create file +DATA/scprd/onlinelog/standby10.log

ORA-15005: name "scprd/onlinelog/standby10.log" is already used by an existing

alias

SQL> alter database add standby logfile thread 2 group 10 '+DATA/scprd/onlinelog/standby10.log' size 250M  reuse;

Database altered.

添加完成日志组。

备库执行：

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

开始同步：

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT

Attempt to start background Managed Standby Recovery process (SCPRDDG)

Fri Oct 13 00:10:53 2017

MRP0 started with pid=48, OS id=30390

MRP0: Background Managed Standby Recovery process started (SCPRDDG)

started logmerger process

Fri Oct 13 00:10:58 2017

Managed Standby Recovery not using Real Time Apply

Parallel Media Recovery started with 4 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Media Recovery Log /oracle/arch/1_45614_930582072.dbf

Media Recovery Log /oracle/arch/2_46396_930582072.dbf

Media Recovery Log /oracle/arch/1_45615_930582072.dbf

Media Recovery Log /oracle/arch/1_45616_930582072.dbf

Media Recovery Log /oracle/arch/1_45617_930582072.dbf

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT

Media Recovery Log /oracle/arch/2_46397_930582072.dbf

Media Recovery Log /oracle/arch/1_45618_930582072.dbf

Media Recovery Log /oracle/arch/1_45619_930582072.dbf

Media Recovery Log /oracle/arch/1_45620_930582072.dbf

Media Recovery Log /oracle/arch/2_46398_930582072.dbf

Media Recovery Log /oracle/arch/1_45621_930582072.dbf

Media Recovery Log /oracle/arch/1_45622_930582072.dbf

Media Recovery Log /oracle/arch/1_45623_930582072.dbf

Media Recovery Log /oracle/arch/2_46399_930582072.dbf

Media Recovery Log /oracle/arch/1_45624_930582072.dbf

SQL> show parameter standby ;

NAME                                 TYPE        VALUE

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

standby_archive_dest                 string      ?/dbs/arch

standby_file_management              string      AUTO

SQL> show parameter convert;

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string      +DATA/scprd/datafile, /oracle/

oradata

log_file_name_convert                string      +DATA/scprd/onlinelog, /oracle

/oradata

修改DG的同步参数：

SQL> alter system set standby_file_management = manual;

System altered.

SQL> show parameter standby ;

NAME                                 TYPE        VALUE

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

standby_archive_dest                 string      ?/dbs/arch

standby_file_management              string      MANUAL

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

手工添加日志组

清理logfile

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database clear logfile group 4;

Database altered.

查看状态：

SQL> select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log;

GROUP#    THREAD#    MEMBERS STATUS               SIZE_M

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

1          1          1 UNUSED                   50

2          1          1 UNUSED                   50

3          2          1 UNUSED                   50

4          2          1 UNUSED                   50

添加日志组：

SQL> alter database add logfile thread 1 group 11 '/oracle/oradata/group_11.399.957222953' size 250M;

Database altered.

SQL> alter database add logfile thread 1 group 12 '/oracle/oradata/group_12.400.957223041' size 250M;

Database altered.

SQL> alter database add logfile thread 1 group 13 '/oracle/oradata/group_13.401.957223051' size 250M;

Database altered.

SQL> alter database add logfile thread 1 group 14 '/oracle/oradata/group_14.402.957223059' size 250M;

Database altered.

SQL> alter database add logfile thread 2 group 15 '/oracle/oradata/group_15.403.957223209' size 250M;

Database altered.

SQL> alter database add logfile thread 2 group 16 '/oracle/oradata/group_16.404.957223315' size 250M;

Database altered.

SQL> alter database add logfile thread 2 group 17 '/oracle/oradata/group_17.405.957223323' size 250M;

Database altered.

SQL> alter database add logfile thread 2 group 18 '/oracle/oradata/group_18.406.957223331' size 250M;

Database altered.

SQL> alter database drop standby logfile group 5;

Database altered.

SQL> alter database drop standby logfile group 6;

Database altered.

SQL> alter database drop standby logfile group 7;

Database altered.

SQL> alter database drop standby logfile group 8;

Database altered.

SQL> select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log;

GROUP#    THREAD#    MEMBERS STATUS               SIZE_M

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

1          1          1 UNUSED                   50

2          1          1 UNUSED                   50

12          1          1 CURRENT                 250

14          1          1 UNUSED                  250

13          1          1 UNUSED                  250

11          1          1 UNUSED                  250

16          2          1 UNUSED                  250

17          2          1 UNUSED                  250

18          2          1 UNUSED                  250

15          2          1 CURRENT                 250

4          2          1 UNUSED                   50

GROUP#    THREAD#    MEMBERS STATUS               SIZE_M

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

3          2          1 UNUSED                   50

12 rows selected.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log;

GROUP#    THREAD#    MEMBERS STATUS               SIZE_M

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

11          1          1 UNUSED                  250

12          1          1 CURRENT                 250

13          1          1 UNUSED                  250

14          1          1 UNUSED                  250

15          2          1 CURRENT                 250

16          2          1 UNUSED                  250

17          2          1 UNUSED                  250

18          2          1 UNUSED                  250

8 rows selected.

SQL> alter database drop standby logfile group 9;

Database altered.

SQL> alter database drop standby logfile group 10;

Database altered.

添加standby log组：

SQL> alter database add standby logfile thread 1 group 1 '/oracle/oradata/standby01.log' size 250M;

Database altered.

SQL> alter database add standby logfile thread 1 group 2 '/oracle/oradata/standby02.log' size 250M;

Database altered.

SQL> alter database add standby logfile thread 1 group 3 '/oracle/oradata/standby03.log' size 250M;

Database altered.

SQL> alter database add standby logfile thread 1 group 4 '/oracle/oradata/standby04.log' size 250M;

Database altered.

SQL> alter database add standby logfile thread 1 group 5 '/oracle/oradata/standby05.log' size 250M;

alter database add standby logfile thread 1 group 5 '/oracle/oradata/standby05.log' size 250M

*

ERROR at line 1:

ORA-00301: error in adding log file '/oracle/oradata/standby05.log' - file

cannot be created

ORA-27038: created file already exists

Additional information: 1

SQL> alter database add standby logfile thread 1 group 5 '/oracle/oradata/standby05.log' size 250M reuse;

Database altered.

SQL> alter database add standby logfile thread 2 group 6 '/oracle/oradata/standby06.log' size 250M;

alter database add standby logfile thread 2 group 6 '/oracle/oradata/standby06.log' size 250M

*

ERROR at line 1:

ORA-00301: error in adding log file '/oracle/oradata/standby06.log' - file

cannot be created

ORA-27038: created file already exists

Additional information: 1

SQL> alter database add standby logfile thread 2 group 6 '/oracle/oradata/standby06.log' size 250M reuse;

Database altered.

SQL> alter database add standby logfile thread 2 group 7 '/oracle/oradata/standby07.log' size 250M;

alter database add standby logfile thread 2 group 7 '/oracle/oradata/standby07.log' size 250M

*

ERROR at line 1:

ORA-00301: error in adding log file '/oracle/oradata/standby07.log' - file

cannot be created

ORA-27038: created file already exists

Additional information: 1

SQL> alter database add standby logfile thread 2 group 7 '/oracle/oradata/standby07.log' size 250M reuse;

Database altered.

SQL> alter database add standby logfile thread 2 group 8 '/oracle/oradata/standby08.log' size 250M reuse;

Database altered.

SQL> alter database add standby logfile thread 2 group 9 '/oracle/oradata/standby09.log' size 250M reuse;

Database altered.

SQL> alter database add standby logfile thread 2 group 10 '/oracle/oradata/standby10.log' size 250M reuse;

Database altered.

备库开启同步：

SQL> alter system set standby_file_management = AUTO;

System altered.

SQL> show parameter standby;

NAME                                 TYPE        VALUE

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

standby_archive_dest                 string      ?/dbs/arch

standby_file_management              string      AUTO

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

用切日志看主备是不是同步正常。

SQL> alter system switch logfile;

System altered.

关于11g RAC和增加日志组大小和数量就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。