버전 비교

이전 버전 1

changes.mady.by.user 윤병훈

에 저장

비교대상

새 버전 2

changes.mady.by.user 윤병훈

에 저장

키

  • 이 줄이 추가되었습니다.
  • 이 줄이 삭제되었습니다.
  • 서식이 변경되었습니다.
목차 영역

목차

 

서버 정보 hosts 파일 저장

코드 블럭
languagebash
titlevi /etc/hosts
linenumberstrue
10.10.50.11     ora_act
10.10.50.21     ora_stb

hosts 파일에 Primary 서버의 정보와 Standby 서버의 정보를 입력합니다.

로그 모드 설정

코드 블럭
title로그 모드 조회
SELECT log_mode FROM v$database;
펼치기
서식 미적용
SQL> SELECT log_mode FROM v$database;

LOG_MODE
------------------------------------
ARCHIVELOG
코드 블럭
titleArchive 모드로 변경하기
linenumberstrue
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
코드 블럭
titleForce Logging 적용
ALTER DATABASE FORCE LOGGING;

Data Guard를 사용하기 위해서는 반드시 Archive log 모드로 구성이 되어 있어야 합니다.

로그 모드를 조회한 후 noarchive 상태이면 반드시 archive 상태로 변경합니다.

추가적으로 force logging 모드로 전환하여, 모든 작업이 강제적으로 로그로 기록될 수 있도록 합니다.

db name 파라미터 조회

코드 블럭
titleDB name 파라미터 값 조회
linenumberstrue
show parameter db_name
show parameter db_unique_name
펼치기
서식 미적용
SQL> show parameter db_name

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_name                              string                            orcl
SQL> show parameter db_unique_name

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_unique_name                       string                            ORCL

db_name 파라미터 값은 모두 동일하지만, db_unique_name은 Primary와 Standby가 달라야 합니다.

구분db_namedb_unique_name
PrimaryorclORCL
StandbyorclORCL_STB

tnsname.ora 설정

코드 블럭
languagebash
titletnsnames.ora
linenumberstrue
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora_act)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

ORCL_STB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora_stb)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL_STB)
      (UR = A)
    )
  )

tnsnames.ora 파일의 내용을 설정합니다.

Primary와 Standby DB에 접속 정보가 저장되며, Primary와 Standby 노드 모두 같은 내용을 사용합니다.

저장 위치는 $ORACLE_HOME/network/admin/tnsnames.ora 입니다.

Data Guard 사용을 위한 파라미터 설정

코드 블럭
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCL_STB)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCL_STB NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STB';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
코드 블럭
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
코드 블럭
ALTER SYSTEM SET FAL_CLIENT=ORCL;
ALTER SYSTEM SET FAL_SERVER=ORCL_STB;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

 

Standby 서버의 컨트롤, 파라미터 파일 생성

코드 블럭
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/orcl_stb.ctl';
CREATE PFILE='/home/oracle/orcl_stb.ora' FROM SPFILE;
펼치기
서식 미적용
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/orcl_stb.ctl';

Database altered.

SQL> CREATE PFILE='/home/oracle/orcl_stb.ora' FROM SPFILE;

File created.

 

Standby 파라미터 파일 수정

코드 블럭
languagebash
title파라미터 수정
linenumberstrue
*.db_unique_name='ORCL_STB'
*.fal_client='ORCL_STB'
*.fal_server='ORCL'
*.log_archive_dest_2='SERVICE=ORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'

생성한 orcl_stb.ora 파일에서 다음의 파라미터 값들을 수정합니다.

Standby로 파일 복제

코드 블럭
languagebash
titleStandby에서 Primary의 파일 복제하기
linenumberstrue
scp oracle@ora_act:/home/oracle/orcl_stb.ctl /oradata/orcl/control01.ctl
cp /oradata/orcl/control01.ctl /oradata/fast_recovery_area/orcl/control02.ctl
scp oracle@ora_act:/home/oracle/orcl_stb.ora /home/oracle/orcl_stb.ora
scp oracle@ora_act:$ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs
펼치기
서식 미적용
[oracle@orcl ~]$ scp oracle@ora_act:/home/oracle/orcl_stb.ctl /oradata/orcl/control01.ctl
The authenticity of host 'ora_act (10.10.50.11)' can't be established.
RSA key fingerprint is a9:cf:9d:a5:a3:b4:8b:7b:82:a8:0f:08:84:2f:19:e0.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'ora_act,10.10.50.11' (RSA) to the list of known hosts.
oracle@ora_act's password:
orcl_stb.ctl                                  100% 9520KB   9.3MB/s   00:00


[oracle@orcl ~]$ cp /oradata/orcl/control01.ctl /oradata/fast_recovery_area/orcl/control02.ctl
 
[oracle@orcl ~]$ scp oracle@ora_act:/home/oracle/orcl_stb.ora /home/oracle/orcl_stb.ora
oracle@ora_act's password:
orcl_stb.ora                                  100% 1296     1.3KB/s   00:00


[oracle@orcl ~]$ scp oracle@ora_act:$ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs
oracle@ora_act's password:
orapworcl                                     100% 1536     1.5KB/s   00:00

 

Standby Log 파일 생성

코드 블럭
ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/orcl/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/orcl/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/oradata/orcl/standby_redo03.log') SIZE 50M;

 

Standby의 listener.ora 파일 생성

코드 블럭
languagebash
titlelistener.ora
linenumberstrue
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora_stb)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Standby 쪽의 listener.ora 파일은 아래와 같습니다.

Standby Database 인스턴스 기동

코드 블럭
titleStandby 인스턴스 기동
linenumberstrue
sqlplus / as sysdba
startup nomount pfile='/home/oracle/orcl_stb.ora';
펼치기
서식 미적용
[oracle@orcl ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 14 14:54:26 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/orcl_stb.ora';
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2257880 bytes
Variable Size             545262632 bytes
Database Buffers          289406976 bytes
Redo Buffers                2355200 bytes

 

RMAN 접속

코드 블럭
languagebash
titleRMAN 접속 : Primay나 Standby에서 모두 가능
rman TARGET sys/oracle@ORCL AUXILIARY sys/oracle@ORCL_STB
펼치기
서식 미적용
[oracle@orcl ~]$ rman TARGET sys/oracle@ORCL AUXILIARY sys/oracle@ORCL_STB

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 14 15:04:22 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1511929243)
connected to auxiliary database: ORCL (not mounted)

 

Standby로 데이터베이스 복제

코드 블럭
titleRMAN으로 DB 복제
linenumberstrue
DUPLICATE TARGET DATABASE
   FOR STANDBY
   FROM ACTIVE DATABASE
   DORECOVER
   SPFILE
     SET db_unique_name='ORCL_STB' COMMENT 'Is standby'
     SET LOG_ARCHIVE_DEST_2='SERVICE=ORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'
     SET FAL_SERVER='ORCL' COMMENT 'Is primary'
   NOFILENAMECHECK;
펼치기
서식 미적용
RMAN> DUPLICATE TARGET DATABASE
2>    FOR STANDBY
3>    FROM ACTIVE DATABASE
4>    DORECOVER
5>    SPFILE
6>      SET db_unique_name='ORCL_STB' COMMENT 'Is standby'
7>      SET LOG_ARCHIVE_DEST_2='SERVICE=ORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'
8>      SET FAL_SERVER='ORCL' COMMENT 'Is primary'
9>    NOFILENAMECHECK;

Starting Duplicate Db at 18/08/14
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl' auxiliary format
 '/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl'   targetfile
 '/u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora' auxiliary format
 '/u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora''";
}
executing Memory Script

Starting backup at 18/08/14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=74 device type=DISK
Finished backup at 18/08/14

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_unique_name =
 ''ORCL_STB'' comment=
 ''Is standby'' scope=spfile";
   sql clone "alter system set  LOG_ARCHIVE_DEST_2 =
 ''SERVICE=ORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'' comment=
 '''' scope=spfile";
   sql clone "alter system set  FAL_SERVER =
 ''ORCL'' comment=
 ''Is primary'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_unique_name =  ''ORCL_STB'' comment= ''Is standby'' scope=spfile

sql statement: alter system set  LOG_ARCHIVE_DEST_2 =  ''SERVICE=ORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'' comment= '''' scope=spfile

sql statement: alter system set  FAL_SERVER =  ''ORCL'' comment= ''Is primary'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2257880 bytes
Variable Size                545262632 bytes
Database Buffers             289406976 bytes
Redo Buffers                   2355200 bytes

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/oradata/orcl/control01.ctl';
   restore clone controlfile to  '/oradata/fast_recovery_area/orcl/control02.ctl' from
 '/oradata/orcl/control01.ctl';
}
executing Memory Script

Starting backup at 18/08/14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl.f tag=TAG20180814T150532 RECID=3 STAMP=984150332
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18/08/14

Starting restore at 18/08/14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 18/08/14

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/oradata/orcl/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/oradata/orcl/system01.dbf";
   set newname for datafile  2 to
 "/oradata/orcl/sysaux01.dbf";
   set newname for datafile  3 to
 "/oradata/orcl/undotbs01.dbf";
   set newname for datafile  4 to
 "/oradata/orcl/users01.dbf";
   set newname for datafile  5 to
 "/oradata/orcl/example01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/oradata/orcl/system01.dbf"   datafile
 2 auxiliary format
 "/oradata/orcl/sysaux01.dbf"   datafile
 3 auxiliary format
 "/oradata/orcl/undotbs01.dbf"   datafile
 4 auxiliary format
 "/oradata/orcl/users01.dbf"   datafile
 5 auxiliary format
 "/oradata/orcl/example01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oradata/orcl/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 18/08/14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oradata/orcl/system01.dbf
output file name=/oradata/orcl/system01.dbf tag=TAG20180814T150542
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oradata/orcl/sysaux01.dbf
output file name=/oradata/orcl/sysaux01.dbf tag=TAG20180814T150542
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/oradata/orcl/example01.dbf
output file name=/oradata/orcl/example01.dbf tag=TAG20180814T150542
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/oradata/orcl/undotbs01.dbf
output file name=/oradata/orcl/undotbs01.dbf tag=TAG20180814T150542
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/oradata/orcl/users01.dbf
output file name=/oradata/orcl/users01.dbf tag=TAG20180814T150542
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18/08/14

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/oradata/fast_recovery_area/ORCL/archivelog/2018_08_14/o1_mf_1_10_fq4wh263_.arc" auxiliary format
 "/oradata/fast_recovery_area/ORCL_STB/archivelog/2018_08_14/o1_mf_1_10_%u_.arc"   ;
   catalog clone recovery area;
   switch clone datafile all;
}
executing Memory Script

Starting backup at 18/08/14
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=10 RECID=6 STAMP=984150514
output file name=/oradata/fast_recovery_area/ORCL_STB/archivelog/2018_08_14/o1_mf_1_10_0btahsfi_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 18/08/14

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /oradata/fast_recovery_area/ORCL_STB/archivelog/2018_08_14/o1_mf_1_10_0btahsfi_.arc
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oradata/fast_recovery_area/ORCL_STB/archivelog/2018_08_14/o1_mf_1_10_0btahsfi_.arc

datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=984150515 file name=/oradata/orcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=984150515 file name=/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=984150515 file name=/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=984150515 file name=/oradata/orcl/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=984150515 file name=/oradata/orcl/example01.dbf

contents of Memory Script:
{
   set until scn  996856;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 18/08/14
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file /oradata/fast_recovery_area/ORCL_STB/archivelog/2018_08_14/o1_mf_1_10_0btahsfi_.arc
archived log file name=/oradata/fast_recovery_area/ORCL_STB/archivelog/2018_08_14/o1_mf_1_10_0btahsfi_.arc thread=1 sequence=10
media recovery complete, elapsed time: 00:00:00
Finished recover at 18/08/14
Finished Duplicate Db at 18/08/14

 

스위치오버 : Primary를 Standby로 전환

코드 블럭
titleSwitchover : Primary에서 작업
linenumberstrue
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

 

스위치오버 : Standby를 Primary로 전환

코드 블럭
titleSwitchover : Standby에서 작업
linenumberstrue
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SHUTDOWN IMMEDIATE;
STARTUP;