調整原因

ARC3: Archive log rejected (thread 1 sequence 136480) at host (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XXX.X.XX)(PORT=1530))(CONNECT_DATA=(SERVICE_NAME=)(SERVER=DEDICATED)))
Errors in file //trace/_arc3_7445.trc:
ORA-16401: archivelog rejected by RFS
FAL[server, ARC3]: FAL archive failed, see trace file.
Errors in file //trace/_arc3_7445.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance dbinfo - Archival Error. Archiver continuing.

注意事項

PRIMARY
SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
OPEN pri2101 PRIMARY

SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#;

    GROUP# 大小 (MB)
---------- ----------
         1 50
         2 50
         3 50

SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
GROUP# 大小 (MB)
---------- ----------
         6 50
         4 50
         5 50
        7 50

STANDBY
SQL> select status,instance_name,database_role from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
OPEN std2101 物理STANDBY

SQL> select group#, sum(bytes/1024/1024)"Size in MB" from v$log group by group#;

    GROUP# 大小 (MB)
---------- ----------
         1 50
         2 50
         3 50

SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;

    GROUP# 大小 (MB)
---------- ----------
         6 50
         4 50
         5 50
         7 50

SQL> show parameter standby_file_management

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO

SQL> alter system set standby_file_management=manual;

System altered.

SQL> show parameter standby_file_management

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL

-- PRIMARY database
SQL> select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 size 100M;

Database altered.

--standy database
SQL> select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 CURRENT
3 CLEARING
2 CLEARING
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
SQL> alter database recover managed standby database cancel; ##需停止日志應用，否則會報ORA-01156
SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 size 100M;

Database altered.

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

GROUP# STATUS
---------- ----------------
1 CURRENT
3 CLEARING
2 UNUSED

PRIMARY database

SQL> select group#,status from v$standby_log;

GROUP# STATUS
---------- ----------
4 UNASSIGNED
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED
SQL> alter database drop standby logfile group 4;

Database altered.

SQL> alter database add standby logfile group 4 size 100M;

Database altered.


--standy database
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

GROUP# size in MB
---------- ----------
4 50
5 50
6 50
7 50

SQL> select group#,status from v$standby_log;

GROUP# STATUS
---------- ----------
4 ACTIVE
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

GROUP# size in MB
---------- ----------
1 100
2 100
3 100

SQL> alter system set standby_file_management=auto;

System altered.

SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CLOSING 66
ARCH CONNECTED 0
ARCH CLOSING 63
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 71
RFS IDLE 71
RFS IDLE 0

RAC+DG架構數據庫刪除日志組語句
For RAC database :

Syntax to add /drop group are as Below


SQL>Alter database add standby logfile Thread  group <group no> size <>M;

SQL>Alter database add logfile Thread  group <group no> size <>M;