Control file과 Redo log file 관리

 

-- Control file과 Redo log file 관리
-- 실전 오라클 백업과 복구(생능출판사, 서진수 지음), p.122

-- /u01/app/oracle/disk3/control01.ctl, redo01_a.log, redo02_a.log, redo03_a.log
-- /u01/app/oracle/disk4/control02.ctl, redo01_b.log, redo02_b.log, redo03_b.log
-- /u01/app/oracle/disk5/control03.ctl, redo01_c.log, redo02_c.log, redo03_c.log

SQL> shutdown immediate

SQL> startup open;

SQL> alter system set control_files='/u01/app/oracle/disk1/control01.ctl',
                                                  '/u01/app/oracle/disk2/control02.ctl', 
                                                  '/u01/app/oracle/disk3/control03.ctl',
                                                  '/u01/app/oracle/disk3/control01.ctl',
                                                  '/u01/app/oracle/disk4/control02.ctl',
                                                  '/u01/app/oracle/disk5/control03.ctl' scope=spfile;
SQL> shutdown immediate;
SQL> exit

os]cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/disk1/control01.ctl
os]cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/disk2/control02.ctl
os]cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/disk3/control03.ctl

os]cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/disk3/control01.ctl
os]cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/disk4/control02.ctl
os]cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/disk5/control03.ctl

os]sqlplus / as sysdba

SQL> startup open;
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/disk1/control01.ctl
/u01/app/oracle/disk2/control02.ctl
/u01/app/oracle/disk3/control03.ctl
/u01/app/oracle/disk3/control01.ctl
/u01/app/oracle/disk4/control02.ctl
/u01/app/oracle/disk5/control03.ctl

SQL> alter database add logfile member
     '/u01/app/oracle/disk3/redo01_a.log' to group 1;
SQL> alter database add logfile member
     '/u01/app/oracle/disk3/redo02_a.log' to group 2;
SQL> alter database add logfile member
     '/u01/app/oracle/disk3/redo03_a.log' to group 3;

SQL> alter database add logfile member
     '/u01/app/oracle/disk4/redo01_b.log' to group 1;
SQL> alter database add logfile member
     '/u01/app/oracle/disk4/redo02_b.log' to group 2;
SQL> alter database add logfile member
     '/u01/app/oracle/disk4/redo03_b.log' to group 3;

SQL> alter database add logfile member
     '/u01/app/oracle/disk5/redo01_c.log' to group 1;
SQL> alter database add logfile member
     '/u01/app/oracle/disk5/redo02_c.log' to group 2;
SQL> alter database add logfile member
     '/u01/app/oracle/disk5/redo03_c.log' to group 3;

SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system chechkpoint;

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
     from v$logfile a, v$log b
     where a.group#=b.group#
     order by 1, 2;

    GROUP# MEMBER                                                     MB ARCHIV STATUS
---------- -------------------------------------------------- ---------- ------ ----------------------------
         1 /u01/app/oracle/disk1/redo01_a.log                         50 NO     INACTIVE
         1 /u01/app/oracle/disk2/redo01_b.log                         50 NO     INACTIVE
         1 /u01/app/oracle/disk3/redo01_a.log                         50 NO     INACTIVE
         1 /u01/app/oracle/disk4/redo01_b.log                         50 NO     INACTIVE
         1 /u01/app/oracle/disk5/redo01_c.log                         50 NO     INACTIVE
         2 /u01/app/oracle/disk1/redo02_a.log                         50 NO     CURRENT
         2 /u01/app/oracle/disk2/redo02_b.log                         50 NO     CURRENT
         2 /u01/app/oracle/disk3/redo02_a.log                         50 NO     CURRENT
         2 /u01/app/oracle/disk4/redo02_b.log                         50 NO     CURRENT
         2 /u01/app/oracle/disk5/redo02_c.log                         50 NO     CURRENT
         3 /u01/app/oracle/disk1/redo03_a.log                         50 NO     INACTIVE

    GROUP# MEMBER                                                     MB ARCHIV STATUS
---------- -------------------------------------------------- ---------- ------ ----------------------------
         3 /u01/app/oracle/disk2/redo03_b.log                         50 NO     INACTIVE
         3 /u01/app/oracle/disk3/redo03_a.log                         50 NO     INACTIVE
         3 /u01/app/oracle/disk4/redo03_b.log                         50 NO     INACTIVE
         3 /u01/app/oracle/disk5/redo03_c.log                         50 NO     INACTIVE

SQL> alter database drop logfile member '/u01/app/oracle/disk1/redo01_a.log';
SQL> alter database drop logfile member '/u01/app/oracle/disk2/redo01_b.log';

SQL> alter database drop logfile member '/u01/app/oracle/disk1/redo03_a.log';
SQL> alter database drop logfile member '/u01/app/oracle/disk2/redo03_b.log';

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
     from v$logfile a, v$log b
     where a.group#=b.group#
     order by 1, 2;   2    3    4

    GROUP# MEMBER                                                     MB ARCHIV STATUS
---------- -------------------------------------------------- ---------- ------ ----------------------------
         1 /u01/app/oracle/disk3/redo01_a.log                         50 NO     INACTIVE
         1 /u01/app/oracle/disk4/redo01_b.log                         50 NO     INACTIVE
         1 /u01/app/oracle/disk5/redo01_c.log                         50 NO     INACTIVE
         2 /u01/app/oracle/disk1/redo02_a.log                         50 NO     CURRENT
         2 /u01/app/oracle/disk2/redo02_b.log                         50 NO     CURRENT
         2 /u01/app/oracle/disk3/redo02_a.log                         50 NO     CURRENT
         2 /u01/app/oracle/disk4/redo02_b.log                         50 NO     CURRENT
         2 /u01/app/oracle/disk5/redo02_c.log                         50 NO     CURRENT
         3 /u01/app/oracle/disk3/redo03_a.log                         50 NO     INACTIVE
         3 /u01/app/oracle/disk4/redo03_b.log                         50 NO     INACTIVE
         3 /u01/app/oracle/disk5/redo03_c.log                         50 NO     INACTIVE

SQL> alter system switch logfile;
SQL> alter system checkpoint;

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
     from v$logfile a, v$log b
     where a.group#=b.group#
     order by 1, 2;  2    3    4

    GROUP# MEMBER                                                     MB ARCHIV STATUS
---------- -------------------------------------------------- ---------- ------ ----------------------------
         1 /u01/app/oracle/disk3/redo01_a.log                         50 NO     INACTIVE
         1 /u01/app/oracle/disk4/redo01_b.log                         50 NO     INACTIVE
         1 /u01/app/oracle/disk5/redo01_c.log                         50 NO     INACTIVE
         2 /u01/app/oracle/disk1/redo02_a.log                         50 NO     INACTIVE
         2 /u01/app/oracle/disk2/redo02_b.log                         50 NO     INACTIVE
         2 /u01/app/oracle/disk3/redo02_a.log                         50 NO     INACTIVE
         2 /u01/app/oracle/disk4/redo02_b.log                         50 NO     INACTIVE
         2 /u01/app/oracle/disk5/redo02_c.log                         50 NO     INACTIVE
         3 /u01/app/oracle/disk3/redo03_a.log                         50 NO     CURRENT
         3 /u01/app/oracle/disk4/redo03_b.log                         50 NO     CURRENT
         3 /u01/app/oracle/disk5/redo03_c.log                         50 NO     CURRENT

SQL> alter database drop logfile member '/u01/app/oracle/disk1/redo02_a.log';
SQL> alter database drop logfile member '/u01/app/oracle/disk2/redo02_b.log';

SQL> !rm -rf /u01/app/oracle/disk1/redo01_a.log
SQL> !rm -rf /u01/app/oracle/disk2/redo01_b.log

SQL> !rm -rf /u01/app/oracle/disk1/redo02_a.log
SQL> !rm -rf /u01/app/oracle/disk2/redo02_b.log

SQL> !rm -rf /u01/app/oracle/disk1/redo03_a.log
SQL> !rm -rf /u01/app/oracle/disk2/redo03_b.log

SQL> !ls /u01/app/oracle/disk1/
SQL> !ls /u01/app/oracle/disk2/

SQL>