DB/Oracle
[ADMIN] Archive log mode 설정 및 경로 설정
밍구멍구
2023. 1. 9. 13:48
Archive log
리두 로그 파일과 더불어 중요한 복구 방법 중 하나는 아카이브 로그다. 리두 로그 파일을 따로 저장하느냐 안하느냐에 따라 '노아카이브 로그 모드' 와 '아카이브 로그 모드'로 불리운다. 이중 아카이브 로그 모드가 리두 로그 파일을 별도로 저장하는 모드로 설정한 경로에 따라 리두로그 파일을 복사하는 것을 Archiver 즉, ARCH 백그라운드 프로세스라 부른다.
<노 아카이브 로그 모드 VS 아카이브 로그 모드 차이점>
| Database mode | NoArchiveLog Mode | ArchiveLog Mode |
| Disk I/O | Decrease (Normal) | Increase |
| Online-Backup (Hot Backup) | No | Yes |
| Route | No | Yes |
| Backup Point | Recently Backup | Continually Backup |
| Dataloss | loss | not loss |
※ Archivelog mode 주의사항
Archive log mode에서 리두 로그 그룹이 스위치할때, 아카이브 로그를 쓰게 되어있다. 하지만 이 작업이 완료되지 않았을 경우 리두 로그 버퍼에 있는 리두 로그를 LGWR가 디스크에 쓸수 없게 되면서 변경된 DML 내용에 대해 작업을 할려는 리두 로그 그룹에 대해서는 로그 스위치가 일어나도 작업이 불가능 하기에 데이터 베이스를 사용할 수 없게 된다. 이러한 행 현상은 ArchiveLog를 저장할 공간이 부족할 경우에도 발생하게된다.
해결 방법으로는 ARCH 백그라운드 프로세스를 증가시켜 속도를 올려주는 방법, 리두 로그 그룹의 갯수를 증가시켜 크기 자체를 늘려서 로그 스피닝(Spinning) 현상을 늦추는 것이다. 또한, 디스크의 장애나 사용자 실수로 인한 아카이브 로그 파일의 손실을 막기 위해 여러곳으로 분산해서 저장하는 것 또한 해결 방법이다.
작업 요약
1. Parameter 확인
2. Instance 상태 확인
3. Archive Log Mode 확인
4. Archive Log File이 쌓일 Directory 확인
5. Parameter 변경
6. DB Shutdown
7. DB Mount 후, Archive Log Mode 변경
8. DB Open 후, Archive Log Mode 확인
1. spfile 사용 유무 확인
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string /ora_engine/oracle/11g/dbhome_
1/dbs/spfileORA11GE.ora2. Instance 상태 확인
SQL> select instance_name,status
2 from v$instance;
INSTANCE_NAME STATUS
-------------------------------- ------------------------
ORA11GE OPEN3. Archielog mode 확인
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 14. Archive Log File이 쌓일 Directory 확인
(Archive 용량 관리를 위해 운영시 파티셔닝 할 필요 있음)
- 리눅스 설치시 새로 생성
- 기존에 있는 내역 새로 파티셔닝
- 디스크 추가 및 파티셔닝 후 새로 파티셔닝
4-1 디렉토리 생성
[root@oraclevm ~]# mkdir /ARCH
4-2 권한 부여
[root@oraclevm ~]# chown ora11ge. /ARCH[더보기 내용은 기록용으로 넘겨도되는 부분]
더보기
- 디스크하나 추가하여 마운트 진행한 case
[root@oraclevm ~]# fdisk -l
Disk /dev/sda: 322.1 GB, 322122547200 bytes, 629145600 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x000f1844
Device Boot Start End Blocks Id System
/dev/sda1 * 2048 2099199 1048576 83 Linux
/dev/sda2 2099200 629145599 313523200 8e Linux LVM
Disk /dev/sdb: 53.7 GB, 53687091200 bytes, 104857600 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk /dev/mapper/ol-root: 260.9 GB, 260910874624 bytes, 509591552 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk /dev/mapper/ol-swap: 6442 MB, 6442450944 bytes, 12582912 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk /dev/mapper/ol-home: 53.7 GB, 53687091200 bytes, 104857600 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
[root@oraclevm ~]# fdisk /dev/sdb
Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.
Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0xc7a872db.
Command (m for help): n
Partition type:
p primary (0 primary, 0 extended, 4 free)
e extended
Select (default p): p
Partition number (1-4, default 1): 1
First sector (2048-104857599, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-104857599, default 104857599):
Using default value 104857599
Partition 1 of type Linux and of size 50 GiB is set
Command (m for help): e
e: unknown command
Command action
a toggle a bootable flag
b edit bsd disklabel
c toggle the dos compatibility flag
d delete a partition
g create a new empty GPT partition table
G create an IRIX (SGI) partition table
l list known partition types
m print this menu
n add a new partition
o create a new empty DOS partition table
p print the partition table
q quit without saving changes
s create a new empty Sun disklabel
t change a partition's system id
u change display/entry units
v verify the partition table
w write table to disk and exit
x extra functionality (experts only)
Command (m for help): t
Selected partition 1
Hex code (type L to list all codes): L
0 Empty 24 NEC DOS 81 Minix / old Lin bf Solaris
1 FAT12 27 Hidden NTFS Win 82 Linux swap / So c1 DRDOS/sec (FAT-
2 XENIX root 39 Plan 9 83 Linux c4 DRDOS/sec (FAT-
3 XENIX usr 3c PartitionMagic 84 OS/2 hidden C: c6 DRDOS/sec (FAT-
4 FAT16 <32M 40 Venix 80286 85 Linux extended c7 Syrinx
5 Extended 41 PPC PReP Boot 86 NTFS volume set da Non-FS data
6 FAT16 42 SFS 87 NTFS volume set db CP/M / CTOS / .
7 HPFS/NTFS/exFAT 4d QNX4.x 88 Linux plaintext de Dell Utility
8 AIX 4e QNX4.x 2nd part 8e Linux LVM df BootIt
9 AIX bootable 4f QNX4.x 3rd part 93 Amoeba e1 DOS access
a OS/2 Boot Manag 50 OnTrack DM 94 Amoeba BBT e3 DOS R/O
b W95 FAT32 51 OnTrack DM6 Aux 9f BSD/OS e4 SpeedStor
c W95 FAT32 (LBA) 52 CP/M a0 IBM Thinkpad hi eb BeOS fs
e W95 FAT16 (LBA) 53 OnTrack DM6 Aux a5 FreeBSD ee GPT
f W95 Ext'd (LBA) 54 OnTrackDM6 a6 OpenBSD ef EFI (FAT-12/16/
10 OPUS 55 EZ-Drive a7 NeXTSTEP f0 Linux/PA-RISC b
11 Hidden FAT12 56 Golden Bow a8 Darwin UFS f1 SpeedStor
12 Compaq diagnost 5c Priam Edisk a9 NetBSD f4 SpeedStor
14 Hidden FAT16 <3 61 SpeedStor ab Darwin boot f2 DOS secondary
16 Hidden FAT16 63 GNU HURD or Sys af HFS / HFS+ fb VMware VMFS
17 Hidden HPFS/NTF 64 Novell Netware b7 BSDI fs fc VMware VMKCORE
18 AST SmartSleep 65 Novell Netware b8 BSDI swap fd Linux raid auto
1b Hidden W95 FAT3 70 DiskSecure Mult bb Boot Wizard hid fe LANstep
1c Hidden W95 FAT3 75 PC/IX be Solaris boot ff BBT
1e Hidden W95 FAT1 80 Old Minix
Hex code (type L to list all codes): 8e
Changed type of partition 'Linux' to 'Linux LVM'
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
[root@oraclevm ~]# mkfs -t ext4 /dev/sdb1
mke2fs 1.42.9 (28-Dec-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
3276800 inodes, 13106944 blocks
655347 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=2162163712
400 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000, 7962624, 11239424
Allocating group tables: done
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done
[root@oraclevm ~]# vi /etc/fstab
#
# /etc/fstab
# Created by anaconda on Thu Jan 5 02:38:21 2023
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/ol-root / xfs defaults 0 0
UUID=30bd544c-c663-4af6-95e4-e0d0a102af50 /boot xfs defaults 0 0
/dev/mapper/ol-home /home xfs defaults 0 0
/dev/mapper/ol-swap swap swap defaults 0 0
UUID=wKRf1l-aX7f-De7w-G9fS-tyNG-yvIe-D3AN1t /ARCH ext4 defaults 0 0
[root@oraclevm ~]# df -T
Filesystem Type 1K-blocks Used Available Use% Mounted on
/dev/sdb1 ext4 51473888 53272 48782844 1% /ARCH
[root@oraclevm ~]# fdisk -l
Disk /dev/sda: 322.1 GB, 322122547200 bytes, 629145600 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x000f1844
Device Boot Start End Blocks Id System
/dev/sda1 * 2048 2099199 1048576 83 Linux
/dev/sda2 2099200 629145599 313523200 8e Linux LVM
Disk /dev/sdb: 53.7 GB, 53687091200 bytes, 104857600 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk /dev/mapper/ol-root: 260.9 GB, 260910874624 bytes, 509591552 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk /dev/mapper/ol-swap: 6442 MB, 6442450944 bytes, 12582912 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk /dev/mapper/ol-home: 53.7 GB, 53687091200 bytes, 104857600 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
[root@oraclevm ~]# fdisk /dev/sdb
Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.
Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0xc7a872db.
Command (m for help): n
Partition type:
p primary (0 primary, 0 extended, 4 free)
e extended
Select (default p): p
Partition number (1-4, default 1): 1
First sector (2048-104857599, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-104857599, default 104857599):
Using default value 104857599
Partition 1 of type Linux and of size 50 GiB is set
Command (m for help): e
e: unknown command
Command action
a toggle a bootable flag
b edit bsd disklabel
c toggle the dos compatibility flag
d delete a partition
g create a new empty GPT partition table
G create an IRIX (SGI) partition table
l list known partition types
m print this menu
n add a new partition
o create a new empty DOS partition table
p print the partition table
q quit without saving changes
s create a new empty Sun disklabel
t change a partition's system id
u change display/entry units
v verify the partition table
w write table to disk and exit
x extra functionality (experts only)
Command (m for help): t
Selected partition 1
Hex code (type L to list all codes): L
0 Empty 24 NEC DOS 81 Minix / old Lin bf Solaris
1 FAT12 27 Hidden NTFS Win 82 Linux swap / So c1 DRDOS/sec (FAT-
2 XENIX root 39 Plan 9 83 Linux c4 DRDOS/sec (FAT-
3 XENIX usr 3c PartitionMagic 84 OS/2 hidden C: c6 DRDOS/sec (FAT-
4 FAT16 <32M 40 Venix 80286 85 Linux extended c7 Syrinx
5 Extended 41 PPC PReP Boot 86 NTFS volume set da Non-FS data
6 FAT16 42 SFS 87 NTFS volume set db CP/M / CTOS / .
7 HPFS/NTFS/exFAT 4d QNX4.x 88 Linux plaintext de Dell Utility
8 AIX 4e QNX4.x 2nd part 8e Linux LVM df BootIt
9 AIX bootable 4f QNX4.x 3rd part 93 Amoeba e1 DOS access
a OS/2 Boot Manag 50 OnTrack DM 94 Amoeba BBT e3 DOS R/O
b W95 FAT32 51 OnTrack DM6 Aux 9f BSD/OS e4 SpeedStor
c W95 FAT32 (LBA) 52 CP/M a0 IBM Thinkpad hi eb BeOS fs
e W95 FAT16 (LBA) 53 OnTrack DM6 Aux a5 FreeBSD ee GPT
f W95 Ext'd (LBA) 54 OnTrackDM6 a6 OpenBSD ef EFI (FAT-12/16/
10 OPUS 55 EZ-Drive a7 NeXTSTEP f0 Linux/PA-RISC b
11 Hidden FAT12 56 Golden Bow a8 Darwin UFS f1 SpeedStor
12 Compaq diagnost 5c Priam Edisk a9 NetBSD f4 SpeedStor
14 Hidden FAT16 <3 61 SpeedStor ab Darwin boot f2 DOS secondary
16 Hidden FAT16 63 GNU HURD or Sys af HFS / HFS+ fb VMware VMFS
17 Hidden HPFS/NTF 64 Novell Netware b7 BSDI fs fc VMware VMKCORE
18 AST SmartSleep 65 Novell Netware b8 BSDI swap fd Linux raid auto
1b Hidden W95 FAT3 70 DiskSecure Mult bb Boot Wizard hid fe LANstep
1c Hidden W95 FAT3 75 PC/IX be Solaris boot ff BBT
1e Hidden W95 FAT1 80 Old Minix
Hex code (type L to list all codes): 8e
Changed type of partition 'Linux' to 'Linux LVM'
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
[root@oraclevm ~]# mkfs -t ext4 /dev/sdb1
mke2fs 1.42.9 (28-Dec-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
3276800 inodes, 13106944 blocks
655347 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=2162163712
400 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000, 7962624, 11239424
Allocating group tables: done
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done
[root@oraclevm ~]# vi /etc/fstab
#
# /etc/fstab
# Created by anaconda on Thu Jan 5 02:38:21 2023
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/ol-root / xfs defaults 0 0
UUID=30bd544c-c663-4af6-95e4-e0d0a102af50 /boot xfs defaults 0 0
/dev/mapper/ol-home /home xfs defaults 0 0
/dev/mapper/ol-swap swap swap defaults 0 0
UUID=wKRf1l-aX7f-De7w-G9fS-tyNG-yvIe-D3AN1t /ARCH ext4 defaults 0 0
[root@oraclevm ~]# df -T
Filesystem Type 1K-blocks Used Available Use% Mounted on
/dev/sdb1 ext4 51473888 53272 48782844 1% /ARCH
5. 파라미터 변경
5-1 아카이브 로그 저장 경로 확인
SQL> show parameter log_archive_dest_1;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
5-2 아카이브 로그 저장 경로 변경 및 formating
> fast_recovery_area에 저장하지 않기 위해
SQL> alter system set log_archive_dest_1='location=/ARCH' scope=spfile;
System altered.
SQL> alter system set log_archive_format='arch_%t_%s_%d_%r.arc' scope=spfile;
System altered.6. DB shutdown
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.7. Mount 상태로 기동 후 archive log mode 변경
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1636814848 bytes
Fixed Size 2253744 bytes
Variable Size 486542416 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.8. DB Open 후 Database Mode 확인
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /ARCH
Oldest online log sequence 74
Next log sequence to archive 76
Current log sequence 76