Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 2

OS환경 : Oracle Linux 8.4 (64bit)

 

DB 환경 : Oracle Database 19.12.0.0 ADG

 

OS 및 사전 설정은 아래 게시물 참조

Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 1(https://positivemh.tistory.com/819)

 

방법 : Oracle Linux 8.4에 Oracle 19c ADG(Active Data Guard) 설치 가이드_Part 2

1번 노드(Active) hostname : oel19adg1, db_name : ORAADG, db_unique_name : ORAADG
2번 노드(Standby) hostname : oel19adg2, db_name : ORAADG, db_unique_name : ORAADGDR

 

*본문에 2번노드 표시가 없으면 모두 1번노드에서 작업

 

 

설치파일은 아래 3가지 파일을 이용함(RAC 구성 할때 받아놓은 패치파일 중 grid 패치파일만 빼고 사용함)
DB : LINUX.X64_193000_db_home.zip
OPatch : p6880880_190000_Linux-x86-64.zip(12.2.0.1.27)

RU : p32900083_190000_Linux-x86-64.zip

Patch 32900083 - Combo of OJVM Component Release Update 19.12.0.0.210720 + Grid Infrastructure Jul 2021 Release Update 19.12.0.0.210720

 ㄴ Patch 32895426 - Database Grid Infrastructure Jul 2021 Release Update 19.12.0.0.210720

 ㄴ Patch 32876380 - Oracle JavaVM Component Release Update 19.12.0.0.210720

 

 

DB 소프트웨어(엔진) 설치(1번, 2번 노드 모두 진행)

db 설치 미디어 압축 해제

$ cd $ORACLE_HOME
$ unzip /oracle/media/LINUX.X64_193000_db_home.zip 

 

 

OPatch 파일 최신파일로 교체

$ cd $ORACLE_HOME
$ mv OPatch/ OPatchold
$ unzip /oracle/media/p6880880_190000_Linux-x86-64.zip
$ $ORACLE_HOME/OPatch/opatch version -oh $ORACLE_HOME
OPatch Version: 12.2.0.1.27
 
OPatch succeeded.

 

 

패치파일 압축해제

$ cd /oracle/media
$ unzip p32900083_190000_Linux-x86-64.zip 

 

 

runinstaller 실행(1번노드 모두 설치 후 2번노드 진행)

$ cd $ORACLE_HOME
$ ./runInstaller -applyRU /oracle/media/32900083/32895426/
Preparing the home to patch...
Applying the patch /oracle/media/32900083/32895426/...
(자동으로 패치 적용 후 GUI 창이 나타남)

gui가 로딩되기까지 시간이 조금 걸림

 

 

gui 로딩중

 

 

Set Up Software Only 선택

 

 

Single instance database installation 선택

 

 

Enterprise Edition 선택

 

 

oracle base 지정

 

 

oraInventroy 경로 지정

 

 

group 는 모두 dba 로 지정

 

 

설치 중 root 권한으로 스크립트 실행하는 부분에서 자동로 스크립트 실행할지 여부 지정 root 패스워드 입력

 

 

사전 요구사항 체크중

 

 

response 파일이 필요하다면 따로 저장 후 Install 

 

 

db 엔진 설치중

 

 

root 계정으로 스크립트 실행할 지 물어보는 메세지 Yes

 

 

Close

db 엔진 설치 완료

 

 

OJVM 패치

$ cd /oracle/media/32900083/32876380/
$ $ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME
Oracle Interim Patch Installer version 12.2.0.1.27
Copyright (c) 2022, Oracle Corporation.  All rights reserved.
 
 
Oracle Home       : /oracle/app/oracle/product/19c
Central Inventory : /oracle/app/oraInventory
   from           : /oracle/app/oracle/product/19c/oraInst.loc
OPatch version    : 12.2.0.1.27
OUI version       : 12.2.0.7.0
Log file location : /oracle/app/oracle/product/19c/cfgtoollogs/opatch/opatch2022-03-28_06-44-54PM_1.log
 
Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   32876380  
 
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
 
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle/app/oracle/product/19c')
 
 
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '32876380' to OH '/oracle/app/oracle/product/19c'
 
Patching component oracle.javavm.server, 19.0.0.0.0...
 
Patching component oracle.javavm.server.core, 19.0.0.0.0...
 
Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...
 
Patching component oracle.rdbms, 19.0.0.0.0...
 
Patching component oracle.javavm.client, 19.0.0.0.0...
Patch 32876380 successfully applied.
Log file location: /oracle/app/oracle/product/19c/cfgtoollogs/opatch/opatch2022-03-28_06-44-54PM_1.log
 
OPatch succeeded.

 

 

DB 패치정보 확인

$ $ORACLE_HOME/OPatch/opatch lspatches -oh $ORACLE_HOME
32876380;OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)
32916816;OCW RELEASE UPDATE 19.12.0.0.0 (32916816)
32904851;Database Release Update : 19.12.0.0.210720 (32904851)
 
OPatch succeeded.

 

 

리스너 생성(1번, 2번 노드 모두 진행)

netca 진행

$ netca

 

 

Listener configuration 선택

 

 

Add 선택

 

 

리스너 이름 지정

 

 

프로토콜 지정

 

 

포트 지정

 

 

No 선택

 

 

Next 선택

 

 

Finish 선택

 

 

DB 생성(1번 노드만 진행)

dbca 진행

$ dbca

 

 

gui 로딩중

 

 

Create a database 선택

 

 

Advanced configuration 선택

 

 

Oracle Single Instance database 선택 후 Custom Database 선택

 

 

SID 입력

 

 

데이터 저장영역 선택 

 

 

FRA만 선택, 아카이브 사용하지 않음(나중에 설정)

 

 

리스너 선택

 

 

componets 필요시 선택 후 Next

 

 

메모리 설정

 

 

Character sets - Choose from the list of chracter sets - KO16MSWIN949 선택

 

 

Connection mode - Ddedicated server mode 선택 Next

 

 

체크 해제 후 Next

 

 

패스워드 입력

 

 

Yes

 

 

Create database 선택 후 Next

 

 

Finish

 

 

db 생성중​

 

 

DB 확인

SQL> select instance_name, status from v$instance;
 
INSTANCE_NAME     STATUS
---------------- ------------
ORAADG        OPEN

정상적으로 생성됨

 

 

db 패치 적용

$ cd $ORACLE_HOME/OPatch
$ ./datapatch -verbose

 

 

pfile 생성 후 db 종료(1번 노드), spfile 이름변경

SQL> create pfile from spfile;
 
File created.
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
$ cd $ORACLE_HOME/dbs/
$ mv spfileORAADG.ora spfileORAADG.ora.bak

 

 

데이터 가드 구성 설정

아카이브 경로 생성(1번, 2번 노드)

1번 노드
$ mkdir -p /oracle/app/oracle/arch/adg1
 
2번 노드
$ mkdir -p /oracle/app/oracle/arch/adg2

 

 

adump, oradata, fra 경로 생성(2번 노드)

$ mkdir -p /oracle/app/oracle/admin/ORAADGDR/adump
$ mkdir -p /oracle/app/oracle/oradata/ORAADGDR
$ mkdir -p /oracle/app/oracle/fast_recovery_area/ORAADGDR

 

 

pfile 확인(1번 노드)

$ cd $ORACLE_HOME/dbs/
$ ls 
hc_ORAADG.dat  init.ora  initORAADG.ora  lkORAADG  orapwORAADG  spfileORAADG.ora.bak

 

 

pfile 내용 확인(1번 노드)

기존

$ vi initORAADG.ora 
ORAADG.__data_transfer_cache_size=0
ORAADG.__db_cache_size=1828716544
ORAADG.__inmemory_ext_roarea=0
ORAADG.__inmemory_ext_rwarea=0
ORAADG.__java_pool_size=0
ORAADG.__large_pool_size=16777216
ORAADG.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
ORAADG.__pga_aggregate_target=838860800
ORAADG.__sga_target=2516582400
ORAADG.__shared_io_pool_size=134217728
ORAADG.__shared_pool_size=520093696
*.audit_file_dest='/oracle/app/oracle/admin/ORAADG/adump'
*.audit_trail='none'
*.compatible='19.0.0'
*.control_files='/oracle/app/oracle/oradata/ORAADG/control01.ctl','/oracle/app/oracle/oradata/ORAADG/control02.ctl'
*.db_block_size=8192
*.db_name='ORAADG'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORAADGXDB)'
*.local_listener='LISTENER_ORAADG'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=800m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2400m
*.undo_tablespace='UNDOTBS1'*.db_recovery_file_dest_size=7851m*.db_recovery_file_dest='/oracle/app/oracle/fast_recovery_area/' 

 

 

pfile 수정(1번 노드)

수정(23~34번째 줄 추가)

$ vi initORAADG.ora 
*.audit_file_dest='/oracle/app/oracle/admin/ORAADG/adump'
*.audit_trail='none'
*.compatible='19.0.0'
*.control_files='/oracle/app/oracle/oradata/ORAADG/control01.ctl','/oracle/app/oracle/oradata/ORAADG/control02.ctl'
*.db_block_size=8192
*.db_name='ORAADG'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORAADGXDB)'
*.local_listener='LISTENER_ORAADG'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=800m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2400m
*.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest_size=7851m
*.db_recovery_file_dest='/oracle/app/oracle/fast_recovery_area/' 

*.db_unique_name='ORAADG'
*.dg_broker_start=TRUE
*.fal_client='ORAADG'
*.fal_server='ORAADGDR'
*.standby_file_management='auto'
*.log_archive_config='DG_CONFIG=(ORAADG,ORAADGDR)'
*.log_archive_dest_1='LOCATION=/oracle/app/oracle/arch/adg1 valid_for=(all_logfiles, all_roles) db_unique_name=ORAADG'
#*.log_archive_dest_2='SERVICE=ORAADGDR ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=ORAADGDR'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4

*log_archive_dest_2는 broker 설정시 값이 들어가있으면 에러가 나기때문에 주석처리함

 

 

리스너 종료(1번 노드)

$ lsnrctl stop

 

 

리스너 파일 수정(1번 노드)

기존

$ vi listener.ora 
# listener.ora Network Configuration File: /oracle/app/oracle/product/19c/network/admin/listener.ora
# Generated by Oracle configuration tools.
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

 

 

수정(13~20번째 줄 추가)

$ vi listener.ora 
# listener.ora Network Configuration File: /oracle/app/oracle/product/19c/network/admin/listener.ora
# Generated by Oracle configuration tools.
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORAADG)
      (ORACLE_HOME = /oracle/app/oracle/product/19c)
      (SID_NAME = ORAADG)
    )
  )

 

 

tnsnames.ora 파일 생성(1번 노드)

$ cd $ORACLE_HOME/network/admin
$ vi tnsnames.ora
LISTENER_ORAADG =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg1)(PORT = 1521))
 
 
ORAADG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORAADG)
    )
  )
 
ORAADGDR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORAADGDR)
    )
  )

 

 

리스너 기동(1번 노드)

$ lsnrctl start

 

 

db 기동(1번 노드)

SQL> startup mount
ORACLE instance started.
 
Total System Global Area 2516581456 bytes
Fixed Size            8899664 bytes
Variable Size          536870912 bytes
Database Buffers     1962934272 bytes
Redo Buffers            7876608 bytes
Database mounted.
SQL> alter database archivelog;
 
Database altered.
 
SQL> alter database open;
 
Database altered.

 

 

기동 후 force logging 모드 실행(강제 로깅 기능)(1번 노드)

SQL> alter database force logging;
 
Database altered.

 

 

1번 노드 pfile을 2번 노드로 복사(1번 노드)

$ scp initORAADG.ora oel19adg2:/oracle/app/oracle/product/19c/dbs/initORAADGDR.ora
The authenticity of host 'oel19adg2 (192.168.137.20)' can't be established.
ECDSA key fingerprint is SHA256:sjdPi5sB0qgCnhO4Z8Ubftcl47tdoI9pOafXi0H5Na4.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added 'oel19adg2,192.168.137.20' (ECDSA) to the list of known hosts.
oracle@oel19adg2's password: 
initORAADG.ora                                                100% 1641     1.6MB/s   00:00    

 

 

pfile 수정(2번 노드)

$ cd $ORACLE_HOME/dbs
$ vi initORAADGDR.ora
*.audit_file_dest='/oracle/app/oracle/admin/ORAADGDR/adump'
*.audit_trail='none'
*.compatible='19.0.0'
*.control_files='/oracle/app/oracle/oradata/ORAADGDR/control01.ctl','/oracle/app/oracle/oradata/ORAADGDR/control02.ctl'
*.db_block_size=8192
*.db_name='ORAADG'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORAADGDRXDB)'
*.local_listener='LISTENER_ORAADGDR'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=800m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2400m
*.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest_size=7851m
*.db_recovery_file_dest='/oracle/app/oracle/fast_recovery_area/' 

*.db_unique_name='ORAADGDR'
*.db_file_name_convert = '/oracle/app/oracle/oradata/ORAADG/','/oracle/app/oracle/oradata/ORAADGDR/'
*.log_file_name_convert = '/oracle/app/oracle/oradata/ORAADG/','/oracle/app/oracle/oradata/ORAADGDR/'
*.dg_broker_start=TRUE
*.fal_client='ORAADGDR'
*.fal_server='ORAADG'
*.standby_file_management='auto'
*.log_archive_config='DG_CONFIG=(ORAADG,ORAADGDR)'
*.log_archive_dest_1='LOCATION=/oracle/app/oracle/arch/adg2 valid_for=(all_logfiles, all_roles) db_unique_name=ORAADGDR'
#*.log_archive_dest_2='SERVICE=ORAADG ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=ORAADG'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4

*log_archive_dest_2는 broker 설정시 값이 들어가있으면 에러가 나기때문에 주석처리함

 

 

패스워드 파일 복제(1번 노드)

$ scp orapwORAADG oel19adg2:/oracle/app/oracle/product/19c/dbs/orapwORAADGDR

 

 

Standby Log File 생성(1번 노드)

SQL> alter database add standby logfile '/oracle/app/oracle/oradata/ORAADG/standby_redo01.log' size 200m;
 
Database altered.
 
SQL> alter database add standby logfile '/oracle/app/oracle/oradata/ORAADG/standby_redo02.log' size 200m;
 
Database altered.
 
SQL> alter database add standby logfile '/oracle/app/oracle/oradata/ORAADG/standby_redo03.log' size 200m;
 
Database altered.

 

 

redo로그 생성 확인

SQL> 
set lines 200 pages 1000       
col member form a70
select group#, type, member from v$logfile;
 
    GROUP# TYPE    MEMBER
---------- ------- ----------------------------------------------------------------------
     1 ONLINE  /oracle/app/oracle/oradata/ORAADG/redo01.log
     2 ONLINE  /oracle/app/oracle/oradata/ORAADG/redo02.log
     3 ONLINE  /oracle/app/oracle/oradata/ORAADG/redo03.log
     4 STANDBY /oracle/app/oracle/oradata/ORAADG/standby_redo01.log
     5 STANDBY /oracle/app/oracle/oradata/ORAADG/standby_redo02.log
     6 STANDBY /oracle/app/oracle/oradata/ORAADG/standby_redo03.log
 
6 rows selected.

 

 

flashback database 활성화(선택)(fra가 설정되어있고 primary db에서만 설정 가능함)(1번 노드)

SQL> alter database flashback on;
 
Database altered.

 

 

리스너 종료(2번 노드)

$ lsnrctl stop

 

 

리스너 파일 수정(2번 노드)

$ cd $ORACLE_HOME/network/admin
$ vi listener.ora 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg2)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORAADGDR)
      (ORACLE_HOME = /oracle/app/oracle/product/19c)
      (SID_NAME = ORAADGDR)
    )
  )

 

 

tnsnames.ora 파일 생성(2번 노드)

$ cd $ORACLE_HOME/network/admin
$ vi tnsnames.ora
LISTENER_ORAADGDR =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg2)(PORT = 1521))
 
ORAADG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORAADG)
    )
  )
ORAADGDR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel19adg2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORAADGDR)
    )
  )

 

 

리스너 기동(2번 노드)

$ lsnrctl start

 

 

nomount로 기동(2번 노드)

SQL> startup nomount pfile='/oracle/app/oracle/product/19c/dbs/initORAADGDR.ora';
ORACLE instance started.
 
Total System Global Area 2516581456 bytes
Fixed Size            8899664 bytes
Variable Size          536870912 bytes
Database Buffers     1962934272 bytes
Redo Buffers            7876608 bytes

 

 

접속 테스트(1번 노드에서 2번 노드로, 2번 노드에서 1번 노드로)

1번 노드
$ sqlplus sys/oracle@ORAADGDR as sysdba
SQL> select instance_name, status from v$instance;
 
INSTANCE_NAME     STATUS
---------------- ------------
ORAADGDR     STARTED
 
2번 노드
$ sqlplus sys/oracle@ORAADG as sysdba
SQL> select instance_name, status from v$instance;
 
INSTANCE_NAME     STATUS
---------------- ------------
ORAADG         OPEN

 

 

rman 백업 경로 생성(1번, 2번 노드)

$ mkdir -p /oracle/app/oracle/rman

 

 

rman 환경설정(1번 노드)

$ rman target /
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/app/oracle/rman/db_ctl_%F';
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oracle/app/oracle/rman/%d_%U_%T.bk';   

 

 

rman 원격 접속

$ rman target sys/oracle@ORAADG auxiliary sys/oracle@ORAADGDR
 
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Mar 29 06:38:38 2022
Version 19.12.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: ORAADG (DBID=2271272191)
connected to auxiliary database: ORAADG (not mounted)
 
RMAN> 

 

 

duplicate 명령으로 복제

RMAN> duplicate target database for standby from active database;
 
--로그--
Starting Duplicate Db at 29-MAR-22
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=379 device type=DISK
 
contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/oracle/app/oracle/product/19c/dbs/orapwORAADGDR'   ;
}
executing Memory Script
 
Starting backup at 29-MAR-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=269 device type=DISK
Finished backup at 29-MAR-22
 
contents of Memory Script:
{
   restore clone from service  'ORAADG' standby controlfile;
}
executing Memory Script
 
Starting restore at 29-MAR-22
using channel ORA_AUX_DISK_1
 
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORAADG
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oracle/app/oracle/oradata/ORAADGDR/control01.ctl
output file name=/oracle/app/oracle/oradata/ORAADGDR/control02.ctl
Finished restore at 29-MAR-22
 
contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script
 
sql statement: alter database mount standby database
 
contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/oracle/app/oracle/oradata/ORAADGDR/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/oracle/app/oracle/oradata/ORAADGDR/system01.dbf";
   set newname for datafile  2 to 
 "/oracle/app/oracle/oradata/ORAADGDR/sysaux01.dbf";
   set newname for datafile  3 to 
 "/oracle/app/oracle/oradata/ORAADGDR/undotbs01.dbf";
   set newname for datafile  4 to 
 "/oracle/app/oracle/oradata/ORAADGDR/users01.dbf";
   restore
   from  nonsparse   from service 
 'ORAADG'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script
 
executing command: SET NEWNAME
 
renamed tempfile 1 to /oracle/app/oracle/oradata/ORAADGDR/temp01.dbf in control file
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
Starting restore at 29-MAR-22
using channel ORA_AUX_DISK_1
 
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORAADG
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /oracle/app/oracle/oradata/ORAADGDR/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORAADG
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /oracle/app/oracle/oradata/ORAADGDR/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORAADG
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oracle/app/oracle/oradata/ORAADGDR/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORAADG
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oracle/app/oracle/oradata/ORAADGDR/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 29-MAR-22
 
sql statement: alter system archive log current
 
contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
 
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1100589126 file name=/oracle/app/oracle/oradata/ORAADGDR/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=1100589126 file name=/oracle/app/oracle/oradata/ORAADGDR/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1100589126 file name=/oracle/app/oracle/oradata/ORAADGDR/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1100589126 file name=/oracle/app/oracle/oradata/ORAADGDR/users01.dbf
Finished Duplicate Db at 29-MAR-22
--로그--

 

 

db 상태 확인(2번 노드)

SQL> select instance_name, status from v$instance;
 
INSTANCE_NAME     STATUS
---------------- ------------
ORAADGDR     MOUNTED

duplicate 완료 후에는 2번 노드가 mount 상태가됨

 

 

db role 확인

1번 노드
SQL>  select database_role, open_mode from v$database;
 
DATABASE_ROLE     OPEN_MODE
---------------- --------------------
PRIMARY      READ WRITE
 
2번 노드
SQL>  select database_role, open_mode from v$database;
 
DATABASE_ROLE     OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

 

 

파라미터 파일 변경

현재 파라미터 파일 확인(1, 2번 노드)

SQL> show parameter spfile
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string

spfile에 아무런 값이 없으므로 pfile 을 사용중인것

 

 

spfile 생성(1, 2번 노드)

1번 노드
SQL> create spfile from pfile;
 
File created.
 
2번 노드
SQL> create spfile from pfile;
File created.

 

 

양쪽 노드 모두 재기동(1, 2번 노드)

1번 노드
SQL> shutdown immediate
SQL> startup
 
2번 노드
SQL> shutdown immediate
SQL> startup mount

 

 

파라미터 파일 확인(1, 2번 노드)

1번 노드
SQL> show parameter spfile
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string     /oracle/app/oracle/product/19c/dbs/spfileORAADG.ora
 
2번 노드
SQL> show parameter spfile
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string     /oracle/app/oracle/product/19c/dbs/spfileORAADGDR.ora

정상적으로 spfile 사용중임

 

 

configuration 설정

1번 노드 configuration 생성(1번 노드)

$ dgmgrl sys/oracle
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Mar 29 07:20:59 2022
Version 19.12.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
Connected to "ORAADG"
Connected as SYSDBA.
 
DGMGRL> create configuration 'DR_ORAADG' as primary database is 'ORAADG' connect identifier is 'ORAADG';
Configuration "DR_ORAADG" created with primary database "ORAADG"

 

 

2번 노드 추가(1번 노드)

DGMGRL> add database 'ORAADGDR' as connect identifier is 'ORAADGDR';
Database "ORAADGDR" added

 

 

configuration 확인(1번 노드)

DGMGRL> show configuration
 
Configuration - DR_ORAADG
 
  Protection Mode: MaxPerformance
  Members:
  ORAADG   - Primary database
    ORAADGDR - Physical standby database 
 
Fast-Start Failover:  Disabled
 
Configuration Status:
DISABLED

아직 Configuration Status가 DISABLED 상태임

 

 

configuration 활성화(1번 노드)

DGMGRL> enable configuration
Enabled.

 

 

enable 후 바로 조회 했을 때는 에러가 발생했지만 잠시후 시도하니 에러가 사라짐

에러가 사라지지 않을 경우 노드2(standby db) shutdown 후 startup mount로 재기동

DGMGRL> show configuration
 
Configuration - DR_ORAADG
 
  Protection Mode: MaxPerformance
  Members:
  ORAADG   - Primary database
    Warning: ORA-16905: The member was not enabled yet.
 
    ORAADGDR - Physical standby database 
      Error: ORA-16810: multiple errors or warnings detected for the member
 
Fast-Start Failover:  Disabled
 
Configuration Status:
ERROR   (status updated 51 seconds ago)
 
잠시후 다시 조회(1분 미만)
DGMGRL> show configuration
 
Configuration - DR_ORAADG
 
  Protection Mode: MaxPerformance
  Members:
  ORAADG   - Primary database
    ORAADGDR - Physical standby database 
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 55 seconds ago)

정상

 

 

broker에 등록된 정보 조회(database)

DGMGRL> SHOW DATABASE 'ORAADG';
 
Database - ORAADG
 
  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    ORAADG
 
Database Status:
SUCCESS
 
DGMGRL> SHOW DATABASE 'ORAADGDR';
 
Database - ORAADGDR
 
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 41.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    ORAADG
 
Database Status:
SUCCESS

모두 정상

 

 

해당 시점에 1번노드 alert log 확인 시 처음에 에러가 발생하다가 잠시후 log_archive_dest_2, log_archive_dest_state_2, fal_server 파라미터를 자동으로 설정(변경)해줌

$ cd /oracle/app/oracle/diag/rdbms/oraadg/ORAADG/trace
$ vi alert_ORAADG.log 
***********************************************************************
 
Fatal NI connect error 12521, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORAADGDR)(INSTANCE_NAME=ORAADG)(CID=(PROGRAM=oracle)(HOST=oel19adg1)(USER=oracle))))
 
  VERSION INFORMATION:
    TNS for Linux: Version 19.0.0.0.0 - Production
    TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
  Version 19.12.0.0.0
  Time: 29-MAR-2022 08:34:31
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12564
    
TNS-12564: TNS:connection refused
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
2022-03-29T08:34:31.887056+09:00
Starting background process RSM0
2022-03-29T08:34:31.893649+09:00
RSM0 started with pid=71, OS id=5248 
2022-03-29T08:34:36.082773+09:00
RSM0 (PID:5248): Using STANDBY_ARCHIVE_DEST parameter default value as /oracle/app/oracle/arch/adg1 [krsd.c:18206]
2022-03-29T08:34:36.087193+09:00
ALTER SYSTEM SET log_archive_dest_2='service="ORAADGDR"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="ORAADGDR" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH;
2022-03-29T08:34:36.101786+09:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
2022-03-29T08:34:36.118017+09:00
ALTER SYSTEM SET fal_server='' SCOPE=BOTH;
2022-03-29T08:34:37.439623+09:00
Thread 1 advanced to log sequence 20 (LGWR switch),  current SCN: 828000
  Current log# 2 seq# 20 mem# 0: /oracle/app/oracle/oradata/ORAADG/redo02.log
2022-03-29T08:34:37.528132+09:00
ARC2 (PID:4926): Archived Log entry 11 added for T-1.S-19 ID 0x876117ff LAD:1
2022-03-29T08:36:33.851107+09:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
2022-03-29T08:36:33.859152+09:00
ALTER SYSTEM SET fal_server='' SCOPE=BOTH;

 

 

로그스위치 확인

1번, 2번 노드 모두 alert log tail 로 확인

1번 노드
$ tail -f /oracle/app/oracle/diag/rdbms/oraadg/ORAADG/trace/alert_ORAADG.log 
 
2번 노드
$ tail -f /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/alert_ORAADGDR.log 

 

 

1번 노드에서 로그 스위치 실행(1번 노드)

SQL> alter system switch logfile;
 
System altered.

 

 

1번, 2번 노드 모두 alert log tail 로 확인

1번 노드
2022-03-29T08:50:51.953505+09:00
Thread 1 advanced to log sequence 21 (LGWR switch),  current SCN: 830399
  Current log# 3 seq# 21 mem# 0: /oracle/app/oracle/oradata/ORAADG/redo03.log
2022-03-29T08:50:51.988815+09:00
ARC0 (PID:4898): Archived Log entry 13 added for T-1.S-20 ID 0x876117ff LAD:1
 
2번 노드
2022-03-29T08:50:52.108070+09:00
 rfs (PID:4476): Selected LNO:4 for T-1.S-21 dbid 2271272191 branch 1100546751
2022-03-29T08:50:52.134717+09:00
PR00 (PID:4499): Media Recovery Waiting for T-1.S-21 (in transit)
2022-03-29T08:50:52.134960+09:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 21 Reading mem 0
  Mem# 0: /oracle/app/oracle/oradata/ORAADGDR/standby_redo01.log
2022-03-29T08:50:52.141276+09:00
ARC0 (PID:4363): Archived Log entry 3 added for T-1.S-20 ID 0x876117ff LAD:1

 

 

db 상태 확인

1번 노드
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from v$database;
DB_UNIQUE_NAME               OPEN_MODE        DATABASE_ROLE    SWITCHOVER_STATUS      DATAGUAR
------------------------------ -------------------- ---------------- -------------------- --------
ORAADG                   READ WRITE        PRIMARY         TO STANDBY       ENABLED
 
2번 노드
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from v$database;
DB_UNIQUE_NAME               OPEN_MODE        DATABASE_ROLE    SWITCHOVER_STATUS      DATAGUAR
------------------------------ -------------------- ---------------- -------------------- --------
ORAADGDR               MOUNTED            PHYSICAL STANDBY NOT ALLOWED      ENABLED

Oracle Data Guard 구성 완료(Standby db가 mount 상태)

 

 

Active Data Guard로 전환(Standby db를 open 상태로 전환)

Managed Recovery Process 일시 중지(2번 노드)

SQL> alter database recover managed standby database cancel;
 
Database altered.

 

 

Standby 데이터베이스를 open(2번 노드)

SQL> alter database open;
 
Database altered.

 

 

Managed Recovery Process를 다시 기동(2번 노드)

SQL> alter database recover managed standby database using current logfile disconnect;
 
Database altered.

 

 

db 상태 확인

1번 노드
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from v$database;
DB_UNIQUE_NAME               OPEN_MODE        DATABASE_ROLE    SWITCHOVER_STATUS      DATAGUAR
------------------------------ -------------------- ---------------- -------------------- --------
ORAADG                   READ WRITE        PRIMARY         TO STANDBY       ENABLED
 
2번 노드
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from v$database;
 
DB_UNIQUE_NAME               OPEN_MODE        DATABASE_ROLE    SWITCHOVER_STATUS      DATAGUAR
------------------------------ -------------------- ---------------- -------------------- --------
ORAADGDR               READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED      ENABLED

active db 상태는 그대로지만 standby db는 open_mode가 read only로 변경됨

 

 

에러 방지를 위한 추가 설정

기존 StaticConnectIdentifierd 확인(1번 노드)

$ dgmgrl sys/oracle
DGMGRL> SHOW instance verbose 'ORAADG' on database 'ORAADG';
 
Instance 'ORAADG' of database 'ORAADG'
 
  PFILE:     
  Properties:
    HostName                        = 'oel19adg1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORAADG_DGMGRL)(INSTANCE_NAME=ORAADG)(SERVER=DEDICATED)))'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'
 
  Log file locations:
    Alert log               : /oracle/app/oracle/diag/rdbms/oraadg/ORAADG/trace/alert_ORAADG.log
    Data Guard Broker log   : /oracle/app/oracle/diag/rdbms/oraadg/ORAADG/trace/drcORAADG.log
 
Instance Status:
SUCCESS
 
DGMGRL> SHOW instance verbose 'ORAADG' on database 'ORAADGDR';
 
Instance 'ORAADG' of database 'ORAADGDR'
 
  PFILE:     
  Properties:
    HostName                        = 'oel19adg2'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORAADGDR_DGMGRL)(INSTANCE_NAME=ORAADGDR)(SERVER=DEDICATED)))'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'
 
  Log file locations:
    Alert log               : /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/alert_ORAADGDR.log
    Data Guard Broker log   : /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/drcORAADGDR.log
 
Instance Status:
SUCCESS

현재 기존 StaticConnectIdentifierd 값으로는 switchover시 제대로 통신이 되지않아 에러가 발생함(SERVICE_NAME이 각각 ORAADG_DGMGRL, ORAADGDR_DGMGRL로 되어있음)

$ORACLE_HOME/network/admin/tnstnames.ora에 있는 tns 별칭을 넣어줘야함

 

 

현재 등록된 identifier가 사용가능한(통신가능한) 상태인지 확인(1번 노드)

DGMGRL> validate static connect identifier for 'ORAADG';
Oracle Clusterware is not configured on database "ORAADG".
Connecting to database "ORAADG" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORAADG_DGMGRL)(INSTANCE_NAME=ORAADG)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORAADG_DGMGRL)(INSTANCE_NAME=ORAADG)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
 
Failed.
 
DGMGRL> validate static connect identifier for 'ORAADGDR';
Oracle Clusterware is not configured on database "ORAADGDR".
Connecting to database "ORAADGDR" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel19adg2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORAADGDR_DGMGRL)(INSTANCE_NAME=ORAADGDR)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel19adg2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORAADGDR_DGMGRL)(INSTANCE_NAME=ORAADGDR)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
 
Failed.

ORAADG, ORAADGDR 모두 사용 불가함(Failed.)

 

 

StaticConnectIdentifierd 변경($ORACLE_HOME/network/admin/tnstnames.ora에 있는 tns 별칭으로 변경)(1번 노드)

DGMGRL> edit database 'ORAADG' set property StaticConnectIdentifier = 'ORAADG';
Property "staticconnectidentifier" updated
DGMGRL> edit database 'ORAADGDR' set property StaticConnectIdentifier = 'ORAADGDR';
Property "staticconnectidentifier" updated

정상적으로 변경됨

 

 

현재 등록된 identifier가 사용가능한(통신가능한) 상태인지 재확인(1번 노드)

DGMGRL> validate static connect identifier for 'ORAADG';
Oracle Clusterware is not configured on database "ORAADG".
Connecting to database "ORAADG" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORAADG)(CID=(PROGRAM=dgmgrl)(HOST=oel19adg1)(USER=oracle))(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "ORAADG".
 
DGMGRL> validate static connect identifier for 'ORAADGDR';
Oracle Clusterware is not configured on database "ORAADGDR".
Connecting to database "ORAADGDR" using static connect identifier "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oel19adg2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORAADGDR)(CID=(PROGRAM=dgmgrl)(HOST=oel19adg1)(USER=oracle))(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "ORAADGDR".

ORAADG, ORAADGDR 모두 정상적으로 사용 가능함(Succeeded.)

 

 

StaticConnectIdentifierd 재확인(1번 노드)

DGMGRL> SHOW instance verbose 'ORAADG' on database 'ORAADG';
 
Instance 'ORAADG' of database 'ORAADG'
 
  PFILE:     
  Properties:
    HostName                        = 'oel19adg1'
    StaticConnectIdentifier         = 'ORAADG'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'
 
  Log file locations:
    Alert log               : /oracle/app/oracle/diag/rdbms/oraadg/ORAADG/trace/alert_ORAADG.log
    Data Guard Broker log   : /oracle/app/oracle/diag/rdbms/oraadg/ORAADG/trace/drcORAADG.log
 
Instance Status:
SUCCESS
 
DGMGRL> SHOW instance verbose 'ORAADG' on database 'ORAADGDR';
 
Instance 'ORAADG' of database 'ORAADGDR'
 
  PFILE:     
  Properties:
    HostName                        = 'oel19adg2'
    StaticConnectIdentifier         = 'ORAADGDR'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'
 
  Log file locations:
    Alert log               : /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/alert_ORAADGDR.log
    Data Guard Broker log   : /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/drcORAADGDR.log
 
Instance Status:
SUCCESS

정상적으로 변경됨

 

 

flashback database 활성화(선택)(fra가 설정되어있고 primary db에서만 설정 가능함)(2번 노드)

기존 standby를 switchover 해서 primary로 변경 후 flashback database 활성화

$ dgmgrl sys/oracle
DGMGRL> switchover to ORAADGDR
Performing switchover NOW, please wait...
Operation requires a connection to database "ORAADGDR"
Connecting ...
Connected to "ORAADGDR"
Connected as SYSDBA.
New primary database "ORAADGDR" is opening...
Operation requires start up of instance "ORAADG" on database "ORAADG"
Starting instance "ORAADG"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ORAADG"
Database mounted.
Database opened.
Connected to "ORAADG"
Switchover succeeded, new primary is "oraadgdr"

정상적으로 switchover됨

 

 

configuration 확인

$ dgmgrl sys/oracle
DGMGRL> show configuration
 
Configuration - DR_ORAADG
 
  Protection Mode: MaxPerformance
  Members:
  ORAADGDR - Primary database
    ORAADG   - Physical standby database 
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 13 seconds ago)

정상적으로 switchover됨

 

 

flashback database 활성화(2번 노드)

SQL> alter database flashback on;
 
Database altered.

 

 

flashback database 확인(1번, 2번 노드)

1번 노드
SQL> select db_unique_name, flashback_on from v$database;
 
DB_UNIQUE_NAME               FLASHBACK_ON
------------------------------ ------------------
ORAADG                   YES
 
2번 노드
SQL> select db_unique_name, flashback_on from v$database;
 
DB_UNIQUE_NAME               FLASHBACK_ON
------------------------------ ------------------
ORAADGDR               YES

 

 

다시 switchover 해서 primary, standby 원복

DGMGRL> switchover to ORAADG
Performing switchover NOW, please wait...
Operation requires a connection to database "ORAADG"
Connecting ...
Connected to "ORAADG"
Connected as SYSDBA.
New primary database "ORAADG" is opening...
Operation requires start up of instance "ORAADGDR" on database "ORAADGDR"
Starting instance "ORAADGDR"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ORAADGDR"
Database mounted.
Database opened.
Connected to "ORAADGDR"
Switchover succeeded, new primary is "oraadg"
 
DGMGRL> show configuration
 
Configuration - DR_ORAADG
 
  Protection Mode: MaxPerformance
  Members:
  ORAADG   - Primary database
    ORAADGDR - Physical standby database 
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 49 seconds ago)

 

 

Oracle Active Data Guard 구성 완료

 

 

추가 확인

현재 recovery mode 확인(1번, 2번 노드)

1번 노드
SQL>
set lines 200 pages 1000
select dest_id, status, database_mode, recovery_mode, db_unique_name, gap_status 
from v$archive_dest_status where dest_id <=2;
 
  DEST_ID STATUS    DATABASE_MODE   RECOVERY_MODE             DB_UNIQUE_NAME      GAP_STATUS
---------- --------- --------------- ---------------------------------- -------------------- ------------------------
     1 VALID     OPEN          IDLE                 ORAADG
     2 VALID     OPEN_READ-ONLY  MANAGED REAL TIME APPLY WITH QUERY ORAADGDR             NO GAP
 
2번 노드
SQL>
set lines 200 pages 1000
select dest_id, status, database_mode, recovery_mode, db_unique_name, gap_status 
from v$archive_dest_status where dest_id <=2;
   DEST_ID STATUS    DATABASE_MODE   RECOVERY_MODE            DB_UNIQUE_NAME               GAP_STATUS
---------- --------- --------------- ---------------------------------- ------------------------------ ------------------------
     1 VALID     OPEN_READ-ONLY  MANAGED REAL TIME APPLY        ORAADGDR
     2 INACTIVE  UNKNOWN         IDLE                NONE

1번 노드에서 조회했을때는 1번(본인노드)의 database mode와 recovery mode가 각각 OPEN에 IDLE로 나오고

2번 노드(standby)는 OPEN_READ-ONLY에 MANAGED REAL TIME APPLY WITH QUERY로 나옴

2번 노드에서 조회했을때는 1번(본인노드)의 database mode와 recovery mode가 각각 OPEN_READ-ONLY에 MANAGED REAL TIME APPLY로 나오고

2번 노드는 UNKNOWN에 IDLE로 나옴

 

 

각 노드에서 log_archive_dest1, 2를 확인해보면 2번노드는 log_archive_dest_2가 비어있음(standby db이기때문에 redo log(아카이브로그)를 보낼 필요가 없어서 그런듯함)

1번 노드
SQL> show parameter log_archive_dest_1
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1             string     LOCATION=/oracle/app/oracle/arch/adg1 valid_for=(all_logfiles, all_roles) db_unique_name=ORAADG
 
SQL> show parameter log_archive_dest_2
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2             string     service="ORAADGDR", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="ORAADGDR" 
                                          net_timeout=30, valid_for=(online_logfile,all_roles)
 
2번 노드
SQL> show parameter log_archive_dest_1
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1             string     LOCATION=/oracle/app/oracle/arch/adg2 valid_for=(all_logfiles, all_roles) db_unique_name=ORAADGDR
 
SQL> show parameter log_archive_dest_2
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2             string

 

 

다음 게시글로 이동

다음 게시글에서는 real time redo apply 테스트와 switchover, failover를 테스트함

Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 3(https://positivemh.tistory.com/821)

 

 

참조 :

https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/index.html

https://docs.oracle.com/en/database/oracle/oracle-database/19/dgbkr/index.html

https://positivemh.tistory.com/674

Oracle Linux 6.8에 Oracle 11g R2 ADG 설치 가이드

https://dataforum.io/display/ORCL/Oracle+Database+19c+Data+Guard 

https://positivemh.tistory.com/819

Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 1

https://positivemh.tistory.com/820

Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 2

https://positivemh.tistory.com/821

Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 3