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

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

备库关闭日志应用：

结构化查询语言从五美元实例中选择状态；

状态

–

安装好的

结构化查询语言更改数据库恢复托管备用数据库取消；

数据库被修改。

日志报错：

ORA-16037:用户请求取消托管恢复操作

不使用实时应用的托管备用恢复

恢复中断！

更改时将数据文件恢复到一致状态2463385381

2017年10月12日星期四23:11:02

MRP0:后台介质恢复过程关闭(SCPRDDG)

托管备用恢复已取消

完成：更改数据库恢复托管备用数据库取消

主库：

结构化查询语言从五美元日志中选择组#、线程#、成员、状态、字节/1024/1024作为size _ m；

组#线程#成员状态大小

– – – – –

1 1 1 INACTIVE 50

2 1 1电流50

3 2 1 INACTIVE 50

4 2 1电流50

结构化查询语言选择组#,从五美元日志文件中按一排序的成员；

组号

–

成员

–

一

DATA/scp rd/online og/group _ 1。29990 .999999999996

2

DATA/scp rd/online og/group _ 2。19998 .999999999916

3

DATA/scp rd/online og/group _ 3。29960 .999999999996

组号

–

成员

–

四

DATA/scp rd/online og/group _ 4。19999 .999999999916

5

数据/scprd/online og/stand by 05 . 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和增加日志组大小和数量就分享到这里了，希望