这篇文章给大家介绍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和增加日志组大小和数量就分享到这里了，希望
内容来源网络，如有侵权，联系删除，本文地址：https://www.230890.com/zhan/123897.html