测试修改日志文件大小，备库是否同步

jiawang

测试主库连接日志文件修改大小，备库是否同步

查看日志文件位置，大小

主库：SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/YADATA/redo03.log

/u01/app/oracle/oradata/YADATA/redo02.log

/u01/app/oracle/oradata/YADATA/redo01.log

/u01/app/oracle/oradata/YADATA/std_redo4

/u01/app/oracle/oradata/YADATA/std_redo5

/u01/app/oracle/oradata/YADATA/std_redo6

/u01/app/oracle/oradata/YADATA/std_redo7

7 rows selected.

SQL> select bytes/1024/1024 from v$log;

BYTES/1024/1024

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

            50

            50

            50

SQL> select bytes/1024/1024 fromv$standby_log;

BYTES/1024/1024

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

            50

            50

            50

            50

备库：

SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_3_dvgklzpn_.log

/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_2_dvgklx91_.log

/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_1_dvgklty8_.log

/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_4_dvgkm2dj_.log

/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_5_dvgkm5td_.log

/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_6_dvgkm8x5_.log

/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_7_dvgkmcwz_.log

7 rows selected.

SQL> select bytes/1024/1024 from v$log;

BYTES/1024/1024

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

             50

            50

            50

SQL> select bytes/1024/1024 from v$standby_log;

BYTES/1024/1024

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

            50

            50

            50

                              50

增加每组成员

SQL>  alter database add logfilemember'/u01/app/oracle/oradata/YADATA/redo01_2.dbf' to group 1;

Database altered.

SQL> selectgroup#,thread#,members,archived,status from v$log;

    GROUP#   THREAD#    MEMBERS ARC STATUS

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

         1          1          2 YES INACTIVE

         2          1          1 NO CURRENT

         3          1          1 YES INACTIVE

SQL>  alter database add logfilemember'/u01/app/oracle/oradata/YADATA/redo02_2.dbf' to group 2;

Database altered.

SQL> alterdatabase add logfile member'/u01/app/oracle/oradata/YADATA/redo03_2.dbf' togroup 3;

Database altered.

SQL> selectgroup#,thread#,members,archived,status from v$log;

    GROUP#   THREAD#    MEMBERS ARC STATUS

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

         1          1          2 YES INACTIVE

         2          1          2 NO CURRENT

         3          1          2 YES INACTIVE

SQL> altersystem checkpoint;

System altered.

SQL> selectMEMBER from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/YADATA/redo03.log

/u01/app/oracle/oradata/YADATA/redo02.log

/u01/app/oracle/oradata/YADATA/redo01.log

/u01/app/oracle/oradata/YADATA/std_redo4

/u01/app/oracle/oradata/YADATA/std_redo5

/u01/app/oracle/oradata/YADATA/std_redo6

/u01/app/oracle/oradata/YADATA/std_redo7

/u01/app/oracle/oradata/YADATA/redo01_2.dbf

/u01/app/oracle/oradata/YADATA/redo02_2.dbf

/u01/app/oracle/oradata/YADATA/redo03_2.dbf

10 rows selected.

SQL> select * fromv$logfile order by group#;

GROUP#   STATUS    TYPE   MEMBER                                   IS_

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

         1         ONLINE /u01/app/oracle/oradata/YADATA/redo01.log          NO

         1 INVALID ONLINE /u01/app/oracle/oradata/YADATA/redo01_2.dbf        NO

         2         ONLINE /u01/app/oracle/oradata/YADATA/redo02.log          NO

         2 INVALID ONLINE  /u01/app/oracle/oradata/YADATA/redo02_2.dbf        NO

         3         ONLINE /u01/app/oracle/oradata/YADATA/redo03.log          NO

         3 INVALID ONLINE /u01/app/oracle/oradata/YADATA/redo03_2.dbf        NO

         4         STANDBY/u01/app/oracle/oradata/YADATA/std_redo4          NO

         5         STANDBY/u01/app/oracle/oradata/YADATA/std_redo5           NO

         6         STANDBY/u01/app/oracle/oradata/YADATA/std_redo6           NO

         7         STANDBY /u01/app/oracle/oradata/YADATA/std_redo7           NO

10 rows selected.


验证备库是否实时同步

SQL> shutdownimmediate;

Database closed.

Databasedismounted.

ORACLE instanceshut down.

SQL> startup;

ORACLE instancestarted.

Total System GlobalArea  765833216 bytes

Fixed Size                  1367368 bytes

Variable Size             549454520 bytes

DatabaseBuffers          209715200 bytes

Redo Buffers                5296128 bytes

Database mounted.

Database opened.

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

Database altered.

SQL> select * fromv$logfile order by group#;

    GROUP# STATUS  TYPE   MEMBER                                                                          IS_

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

         1         ONLINE /u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_1_dvgklty8_.log    YES

         2         ONLINE /u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_2_dvgklx91_.log    YES

         3         ONLINE /u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_3_dvgklzpn_.log    YES

         4         STANDBY/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_4_dvgkm2dj_.log    YES

         5         STANDBY/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_5_dvgkm5td_.log    YES

         6         STANDBY/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_6_dvgkm8x5_.log    YES

         7         STANDBY/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_7_dvgkmcwz_.log    YES

7 rows selected.

手工添加备库日志组成员：

SQL> showparameter standby_file_management

NAME                                 TYPE        VALUE

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

standby_file_management              string      AUTO

SQL> altersystem set standby_file_management='MANUAL'；

System altered.

SQL> show parameterstandby_file_management

NAME                                 TYPE        VALUE

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

standby_file_management              string      MANUAL

SQL>

SQL> alterdatabase recover managed standby database cancel; （停止恢复）

Database altered.

SQL>  alter database add logfilemember'/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_1_dvgklty8_2.log'to group 1;

Database altered.

SQL> alterdatabase add logfilemember'/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_2_dvgklx91_2.log'to group 2;

Database altered.

SQL> alterdatabase add logfilemember'/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_3_dvgklzpn_2.log'to group 3;

Database altered.

SQL> altersystem set standby_file_management='auto';

System altered.

SQL> alterdatabase recover managed standby database using current logfile disconnect;  （启动数据同步）

Database altered.

SQL> selectl.group#,l.bytes/1024/1024 mb,lf.member from v$log l,v$logfile lf where l.group#=lf.group#order by group#;

    GROUP#         MB MEMBER

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

         1         50/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_1_dvgklty8_.log

         1         50/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_1_dvgklty8_2.log

         2         50/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_2_dvgklx91_2.log

         2        50/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_2_dvgklx91_.log

         3         50/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_3_dvgklzpn_2.log

         3         50/u01/app/oracle/fast_recovery_area/YADATA_STD/onlinelog/o1_mf_3_dvgklzpn_.log

6 rows selected.

验证主备库数据文件是否同步

主库：

执行手工切换

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

查看归档文件路径

SQL> archivelog list;

Database logmode              Archive Mode

Automaticarchival             Enabled

Archivedestination            /u01/app/archive

Oldest online logsequence     94

Next log sequenceto archive   96

Current logsequence           96

[oracle@strong ~]$cd /u01/app/archive

登录/注册后可看大图