Oracle Archive log 모드 전환

* Oracle Archive log 모드 전환

// nomount - 미사용: control file, data file, log file

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 3256942592 bytes

Fixed Size                  2217752 bytes

Variable Size            1778387176 bytes

Database Buffers         1459617792 bytes

Redo Buffers               16719872 bytes

SQL> archive log list;

ORA-01507: database not mounted

// mount - 사용: control file, 미사용: data file, log file

SQL> alter database mount;

Database altered.

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

System altered.

SQL> alter system set log_archive_dest_1='location=/backup/oracle/archive';

System altered.

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /backup/oracle/archive

Oldest online log sequence     5

Current log sequence           7

SQL> alter database archivelog;

Database altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /backup/oracle/archive

Oldest online log sequence     5

Next log sequence to archive   7

Current log sequence           7

// mount - 사용: control file, data file, log file

SQL> alter database open;

Database altered.

// 강제 log switch (archive 생성)

SQL> select * from v$log

GROUP#	THREAD#	SEQUENCE#	BYTES	BLOCKSIZE	MEMBERS	ARCHIVED	STATUS	FIRST_CHANGE#	FIRST_TIME	NEXT_CHANGE#	NEXT_TIME
1	1	13	52428800	512	1	NO	CURRENT	1037080	2013/05/05 15:09:09	281474976710655
2	1	11	52428800	512	1	YES	INACTIVE	1036888	2013/05/05 15:06:38	1036892	2013/05/05 15:06:43
3	1	12	52428800	512	1	YES	ACTIVE	1036892	2013/05/05 15:06:43	1037080	2013/05/05 15:09:09

SQL> alter system switch logfile;

System altered.

// log switch 및 archive log 생성 확인

SQL> select * from v$log

GROUP#	THREAD#	SEQUENCE#	BYTES	BLOCKSIZE	MEMBERS	ARCHIVED	STATUS	FIRST_CHANGE#	FIRST_TIME	NEXT_CHANGE#	NEXT_TIME
1	1	13	52428800	512	1	YES	ACTIVE	1037080	2013/05/05 15:09:09	1037626	2013/05/05 15:13:08
2	1	14	52428800	512	1	NO	CURRENT	1037626	2013/05/05 15:13:08	281474976710655
3	1	12	52428800	512	1	YES	ACTIVE	1036892	2013/05/05 15:06:43	1037080	2013/05/05 15:09:09

SQL> !ls -al /backup/oracle/archive

-rw-r-----. 1 oracle oinstall   527872 2013-05-05 15:13 1_13_814572421.arc

SQL> alter system set log_archive_dest_state_1=defer;

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system set log_archive_dest_state_1=defer;

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system archive log stop;

System altered.

SQL> alter system archive log start;

System altered.

* Oracle Archive log 위치 변경 (Oracle 재기동 필요없음)

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/backup/oracle/arch2/';

SQL> ALTER SYSTEM SWITCH LOGFILE; -- 정상 적용 확인차, 반드시 할 필요는 없음

         또는 ALTER SYSTEM ARCHIVE LOG CURRENT; -- switching 완료될때까지 대기 (보다 안전)

-- 여러 곳에 logging 시 그 경로도 변경해야 하는 경우 DEST_2, DEST_3 등도 변경해야 함