[제목]
[2025-09-20] Oracle 12cR2 2 Node RAC to Single ADG 구축 (DG Broker 사용안하고 수동으로 동기화)
[테스트 개요]
본 테스트는
Oracle 12cR2 RAC 2노드 Primary에서 단일 Standby로 Active Data Guard(ADG)를 수동 구축하고 실시간
적용(real-time apply) 상태를 검증하는 것을 목적으로 한다.
DG Broker는 사용하지 않으며 전송 모드는 LGWR
ASYNC NOAFFIRM(최대 성능, Maximum Performance)로 설정한다.
테스트 절차는 다음과 같다.
Primary와
Standby 간 TNS 구성을 완료하고 접속을 확인한다.
Primary에서 ADG 관련 파라미터를 설정하고 Standby Redo
Log를 각 스레드별 온라인 리두 로그 수보다 1개 이상 더 추가한다.
RMAN을 이용하여 Full Backup, Archive Log,
Standby Controlfile을 생성하고 Standby 서버로 이관한다.
Standby 서버에서 디렉터리와 권한을 정비한 뒤
Standby Controlfile을 복구하고 데이터파일 Restore와 Switch를 수행한다. 이후 SPFILE 및 PWFILE을 ASM에
배치한다.
Standby 데이터베이스를 CRS에 PHYSICAL_STANDBY로 등록한다.
Standby 데이터베이스를 Managed
Recovery 모드로 기동하며 실시간 적용을 위해 다음 명령을 수행한다.
ALTER DATABASE RECOVER MANAGED
STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM
SESSION;
Primary에서
로그 스위치를 유도한 후 V$DATAGUARD_STATS, GV$ARCHIVE_DEST_STATUS, V$STANDBY_LOG에서 Apply
Lag이 0에 수렴하는지 확인한다.
추가 검증으로 Standby를 READ ONLY OPEN 모드로 전환한 뒤 Managed
Recovery Process를 재시작하여 읽기 전용 서비스와 동기화 재개 동작을 확인한다.
이 과정을 통해 RAC 기반 Primary와 Standby 간의 ADG 수동 구축 절차가 정확히 수행되는지와 실시간 적용이 안정적으로 이루어지는지를 검증한다.
[테스트 환경]
<Primary>
OS : Oracle Linux Server 7.9 (grep
^PRETTY_NAME= /etc/os-release | cut -d= -f2- | tr -d '"')
OS Kernal :
5.4.17-2102.201.3.el7uek.x86_64 (uname -r)
Oracle Version : Oracle Database
12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production (echo -e "set pages
0 lines 200 feedback off heading off\nselect banner from v\$version where banner
like 'Oracle Database%';" | sqlplus -s / as sysdba)
Oracle
Configuration
DB명 : ORA12R1 (echo -e 'set pages 0 feedback
off heading off verify off\nselect '\''DB명 : '\''||name from v$database;' |
sqlplus -s / as sysdba) '
RAC Node
1
Hostname : ol7ora12r11
(hostname)
Public IP : 192.168.240.11 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name :
ORA12R11 (echo -e 'set pages 0 feedback off heading off verify off\nselect
'\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as
sysdba) '
RAC Node 2
Hostname :
ol7ora12r12
Public IP : 192.168.240.11 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name :
ORA12R12 (echo -e 'set pages 0 feedback off heading off verify off\nselect
'\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as
sysdba) '
Patch Info
Grid (opatch lspatches) (grid os
user)
33610989;TOMCAT RELEASE UPDATE 12.2.0.1.0(ID:RELEASE)
(33610989)
26839277;DBWLM RELEASE UPDATE 12.2.0.1.0(ID:170913)
(26839277)
33116894;ACFS JUL 2021 RELEASE UPDATE 12.2.0.1.210720
(33116894)
33678030;OCW JAN 2022 RELEASE UPDATE 12.2.0.1.220118
(33678030)
33587128;Database Jan 2022 Release Update :
12.2.0.1.220118 (33587128)
Oracle (opatch lspatches) (oracle os
user)
33678030;OCW JAN 2022 RELEASE UPDATE 12.2.0.1.220118
(33678030)
33587128;Database Jan 2022 Release Update :
12.2.0.1.220118 (33587128)
<Standby>
OS : Oracle Linux Server 7.9 (grep
^PRETTY_NAME= /etc/os-release | cut -d= -f2- | tr -d '"')
OS Kernal :
5.4.17-2102.201.3.el7uek.x86_64 (uname -r)
Oracle Version : Oracle Database
12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production (echo -e "set pages
0 lines 200 feedback off heading off\nselect banner from v\$version where banner
like 'Oracle Database%';" | sqlplus -s / as sysdba)
Oracle
Configuration
DB명 : ORA12R1 (echo -e
'set pages 0 feedback off heading off verify off\nselect '\''DB명 : '\''||name
from v$database;' | sqlplus
-s / as sysdba) '
--> 이걸로 생성될것임
DB Unique Name : ORA12R1_STB
--> 이걸로
생성될것임
Single
Hostname :
ol7ora12s1 (hostname)
Public IP : 192.168.240.10 (getent
ahostsv4 `hostname` | awk '{print $1; exit}')
Instance Name : ORA12R1 (echo -e 'set
pages 0 feedback off heading off verify off\nselect '\''Instance Name :
'\''||instance_name from v$instance;' | sqlplus -s / as sysdba)
'
--> 이걸로 생성될것임
Patch Info
Grid (opatch lspatches)
(grid os user)
33678030;OCW JAN 2022 RELEASE UPDATE
12.2.0.1.220118 (33678030)
33610989;TOMCAT RELEASE UPDATE
12.2.0.1.0(ID:RELEASE) (33610989)
33116894;ACFS JUL 2021 RELEASE
UPDATE 12.2.0.1.210720 (33116894)
26839277;DBWLM RELEASE UPDATE
12.2.0.1.0(ID:170913) (26839277)
33587128;Database Jan 2022 Release Update :
12.2.0.1.220118 (33587128)
Oracle (opatch lspatches) (oracle os
user)
33678030;OCW JAN 2022 RELEASE UPDATE 12.2.0.1.220118
(33678030)
33587128;Database Jan 2022 Release Update :
12.2.0.1.220118
(33587128)
[내용]
1. 서버 설정
1-1. /etc/hosts 설정
<Primary>
--1번
노드
[root@ol7ora12r11][/root]$ vi /etc/hosts
[root@ol7ora12r11][/root]$
cat /etc/hosts
127.0.0.1 localhost
localhost.localdomain localhost4
localhost4.localdomain4
::1
localhost localhost.localdomain localhost6
localhost6.localdomain6
### Public
IP
192.168.240.11 ol7ora12r11
192.168.240.12
ol7ora12r12
### Private
IP
192.168.11.11 ol7ora12r11-priv
192.168.11.12
ol7ora12r12-priv
### Virtual
IP
192.168.240.14 ol7ora12r11-vip
192.168.240.15
ol7ora12r12-vip
###
DNS
192.168.240.17 ol7ora12r1-scan
192.168.240.18
ol7ora12r1-scan
192.168.240.19 ol7ora12r1-scan
#Standby
192.168.240.10 ol7ora12s1
--> 추가할
것
--2번 노드
[root@ol7ora12r11][/root]$ vi
/etc/hosts
[root@ol7ora12r11][/root]$ cat
/etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4
localhost4.localdomain4
::1
localhost localhost.localdomain localhost6
localhost6.localdomain6
### Public
IP
192.168.240.11 ol7ora12r11
192.168.240.12
ol7ora12r12
### Private
IP
192.168.11.11 ol7ora12r11-priv
192.168.11.12
ol7ora12r12-priv
### Virtual
IP
192.168.240.14 ol7ora12r11-vip
192.168.240.15
ol7ora12r12-vip
###
DNS
192.168.240.17 ol7ora12r1-scan
192.168.240.18
ol7ora12r1-scan
192.168.240.19 ol7ora12r1-scan
#Standby
192.168.240.10 ol7ora12s1
--> 추가할
것
<Standby>
[root@ol7ora12s1][/root]$ vi
/etc/hosts
[root@ol7ora12s1][/root]$ cat
/etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4
localhost4.localdomain4
::1
localhost localhost.localdomain localhost6
localhost6.localdomain6
192.168.240.10 ol7ora12s1
#Primary
192.168.240.11
ol7ora12r11
192.168.240.12 ol7ora12r12
--> 추가할 것
1-2. tnsnames.ora 설정
<Primary>
--1번 노드
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ vi
/u01/app/oracle/product/12c/db_1/network/admin/tnsnames.ora
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$
cat
/u01/app/oracle/product/12c/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration
File: /u01/app/oracle/product/12c/db_1/network/admin/tnsnames.ora
# Generated
by Oracle configuration tools.
ORA12R1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
ol7ora12r1-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME =
ORA12R1)
)
)
#Primary
TNS_ORA12R1
=
(DESCRIPTION =
(ADDRESS_LIST
=
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.240.11)(PORT = 1521))
(ADDRESS = (PROTOCOL =
TCP)(HOST = 192.168.240.12)(PORT =
1521))
(LOAD_BALANCE =
OFF)
(FAILOVER =
ON)
)
(CONNECT_DATA
=
(SERVER =
DEDICATED)
(SERVICE_NAME =
ORA12R1)
(FAILOVER_MODE = (TYPE =
SELECT)(METHOD = BASIC))
)
)
#Standby
TNS_ORA12R1_STB =
(DESCRIPTION
=
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.240.10)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =
ORA12R1_STB)
)
)
--> 추가
--2번
노드
[ORA12R12:oracle@ol7ora12r12][/home/oracle]$ cat
/u01/app/oracle/product/12c/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration
File: /u01/app/oracle/product/12c/db_1/network/admin/tnsnames.ora
# Generated
by Oracle configuration tools.
ORA12R1
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
ol7ora12r1-scan)(PORT = 1521))
(CONNECT_DATA
=
(SERVER =
DEDICATED)
(SERVICE_NAME =
ORA12R1)
)
)
#Primary
TNS_ORA12R1 =
(DESCRIPTION
=
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =
TCP)(HOST = 192.168.240.11)(PORT =
1521))
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.240.12)(PORT =
1521))
(LOAD_BALANCE =
OFF)
(FAILOVER =
ON)
)
(CONNECT_DATA
=
(SERVER =
DEDICATED)
(SERVICE_NAME =
ORA12R1)
(FAILOVER_MODE = (TYPE =
SELECT)(METHOD = BASIC))
)
)
#Standby
TNS_ORA12R1_STB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.240.10)(PORT = 1521))
(CONNECT_DATA
=
(SERVER =
DEDICATED)
(SERVICE_NAME =
ORA12R1_STB)
)
)
--> 추가
<Standby>
[ORA12S1:oracle@ol7ora12s1][/home/oracle]$ vi
/u01/app/oracle/product/12c/db_1/network/admin/tnsnames.ora
[ORA12S1:oracle@ol7ora12s1][/home/oracle]$
cat
/u01/app/oracle/product/12c/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration
File: /u01/app/oracle/product/12c/db_1/network/admin/tnsnames.ora
# Generated
by Oracle configuration tools.
ORA12S1
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
ol7ora12s1)(PORT = 1521))
(CONNECT_DATA
=
(SERVER =
DEDICATED)
(SERVICE_NAME =
ORA12S1)
)
)
LISTENER_ORA12S1 =
(ADDRESS = (PROTOCOL = TCP)(HOST =
ol7ora12s1)(PORT = 1521))
#Standby
TNS_ORA12R1_STB =
(DESCRIPTION
=
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.240.10)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =
ORA12R1_STB)
)
)
#Primary
TNS_ORA12R1 =
(DESCRIPTION =
(ADDRESS_LIST
=
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.240.11)(PORT = 1521))
(ADDRESS = (PROTOCOL =
TCP)(HOST = 192.168.240.12)(PORT =
1521))
(LOAD_BALANCE =
OFF)
(FAILOVER =
ON)
)
(CONNECT_DATA
=
(SERVER =
DEDICATED)
(SERVICE_NAME =
ORA12R1)
(FAILOVER_MODE = (TYPE =
SELECT)(METHOD = BASIC))
)
)
-->
추가
1-3. tnsping 테스트
<Primary 에서 Standby 로 접속 테스트>
--1번 노드
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ tnsping
TNS_ORA12R1_STB
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-SEP-2025 18:16:11
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
Used
TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL =
TCP)(HOST = 192.168.240.10)(PORT = 1521)) (CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = ORA12R1_STB)))
OK (0
msec)
--2번 노드
[ORA12R12:oracle@ol7ora12r12][/home/oracle]$ tnsping
TNS_ORA12R1_STB
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-SEP-2025 18:16:35
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
Used
TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL =
TCP)(HOST = 192.168.240.10)(PORT = 1521)) (CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = ORA12R1_STB)))
OK (0
msec)
<Standby
에서 Primary 로 접속 테스트>
[ORA12S1:oracle@ol7ora12s1][/u01/app/oracle/product/12c/db_1/network/admin]$
tnsping TNS_ORA12R1
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-SEP-2025 18:18:49
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
Used
TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
(PROTOCOL = TCP)(HOST = 192.168.240.11)(PORT = 1521)) (ADDRESS =
(PROTOCOL = TCP)(HOST = 192.168.240.12)(PORT = 1521)) (LOAD_BALANCE =
OFF) (FAILOVER = ON)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
ORA12R1) (FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC))))
OK (10
msec)
2. Primary 설정
2-1. 아카이브 로그 모드 인지 확인
--1번 노드에서
확인
[ol7ora12r11]<SYS@ORA12R11>$
col name for a20
col database_role
for a20
col open_mode for a20
col log_mode for a20
select name,
inst_id, database_role, open_mode, log_mode from
gv$database;
NAME
INST_ID DATABASE_ROLE
OPEN_MODE
LOG_MODE
-------------------- ---------- --------------------
--------------------
--------------------
ORA12R1
1
PRIMARY
READ WRITE
ARCHIVELOG
ORA12R1
2
PRIMARY
READ WRITE
ARCHIVELOG
2 rows selected.
Elapsed: 00:00:00.01
[ol7ora12r11]<SYS@ORA12R11>$ archive log
list
Database log
mode
Archive Mode
Automatic
archival
Enabled
Archive
destination
+FRA1
Oldest online log sequence 3
Next log
sequence to archive 5
Current log
sequence
5
2-2. force_logging이 활성화 상태인지 확인
[ol7ora12r11]<SYS@ORA12R11>$
col
force_logging for a20
col inst_id for
999
select inst_id, force_logging from gv$database;
INST_ID
FORCE_LOGGING
------- --------------------
1 NO
2 NO
--> FORCE_LOGGING 이 활성화 되어 있지
않음
2 rows selected.
Elapsed: 00:00:00.00
[ol7ora12r11]<SYS@ORA12R11>$
col
force_logging for a20
col inst_id for 999
alter database force
logging;
--> force logging
활성화
Database altered.
Elapsed:
00:00:00.03
[ol7ora12r11]<SYS@ORA12R11>$ select inst_id, force_logging from
gv$database;
INST_ID
FORCE_LOGGING
------- --------------------
1 YES
2 YES
2 rows selected.
2-3. Primary DB에 ADG 관련 파라미터 설정
2-3-1. ADG 관련 파라미터 조회 SQL문을 스크립트 파일로 생성
--RAC
1번
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ alias
cdos
alias cdos='cd
/home/oracle/oracle_scripts'
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ mkdir -p
/home/oracle/oracle_scripts
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ vi
/home/oracle/oracle_scripts/adg_par.sql
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$
cat /home/oracle/oracle_scripts/adg_par.sql
SET LINESIZE 300 PAGES 100
COL
VALUE FOR A90
COL NAME FOR A50
SELECT NAME
,
VALUE
FROM V$PARAMETER
WHERE
UPPER(NAME)
IN (
UPPER(TRIM('DB_NAME'
))
,
UPPER(TRIM('DB_UNIQUE_NAME'
))
,
UPPER(TRIM('LOG_ARCHIVE_CONFIG'
))
,
UPPER(TRIM('LOG_ARCHIVE_DEST_1'
))
,
UPPER(TRIM('LOG_ARCHIVE_DEST_2'
))
,
UPPER(TRIM('LOG_ARCHIVE_DEST_STATE_1' ))
, UPPER(TRIM('LOG_ARCHIVE_DEST_STATE_2'
))
,
UPPER(TRIM('REMOTE_LOGIN_PASSWORDFILE'))
, UPPER(TRIM('LOG_ARCHIVE_FORMAT'
))
,
UPPER(TRIM('LOG_ARCHIVE_MAX_PROCESSES'))
,
UPPER(TRIM('FAL_CLIENT'
))
,
UPPER(TRIM('FAL_SERVER'
))
,
UPPER(TRIM('DB_FILE_NAME_CONVERT'
))
,
UPPER(TRIM('LOG_FILE_NAME_CONVERT'
))
,
UPPER(TRIM('STANDBY_FILE_MANAGEMENT'
))
);
--RAC 2번
[ORA12R12:oracle@ol7ora12r12][/home/oracle]$ alias
cdos
alias cdos='cd
/home/oracle/oracle_scripts'
[ORA12R12:oracle@ol7ora12r12][/home/oracle]$ mkdir -p
/home/oracle/oracle_scripts
[ORA12R12:oracle@ol7ora12r12][/home/oracle]$ vi
/home/oracle/oracle_scripts/adg_par.sql
[ORA12R12:oracle@ol7ora12r12][/home/oracle]$
cat /home/oracle/oracle_scripts/adg_par.sql
SET LINESIZE 300 PAGES 100
COL
VALUE FOR A90
COL NAME FOR A50
SELECT NAME
,
VALUE
FROM V$PARAMETER
WHERE
UPPER(NAME)
IN (
UPPER(TRIM('DB_NAME'
))
,
UPPER(TRIM('DB_UNIQUE_NAME'
))
,
UPPER(TRIM('LOG_ARCHIVE_CONFIG'
))
,
UPPER(TRIM('LOG_ARCHIVE_DEST_1'
))
,
UPPER(TRIM('LOG_ARCHIVE_DEST_2'
))
,
UPPER(TRIM('LOG_ARCHIVE_DEST_STATE_1' ))
, UPPER(TRIM('LOG_ARCHIVE_DEST_STATE_2'
))
,
UPPER(TRIM('REMOTE_LOGIN_PASSWORDFILE'))
, UPPER(TRIM('LOG_ARCHIVE_FORMAT'
))
,
UPPER(TRIM('LOG_ARCHIVE_MAX_PROCESSES'))
,
UPPER(TRIM('FAL_CLIENT'
))
,
UPPER(TRIM('FAL_SERVER'
))
,
UPPER(TRIM('DB_FILE_NAME_CONVERT'
))
,
UPPER(TRIM('LOG_FILE_NAME_CONVERT'
))
,
UPPER(TRIM('STANDBY_FILE_MANAGEMENT'
))
);
2-3-2. Primary DB의 ADG 관련 파라미터 상황
조회
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 18 11:00:17 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production
[ol7ora12r11]<SYS@ORA12R11>$ @/home/oracle/oracle_scripts/adg_par.sql
NAME
VALUE
--------------------------------------------------
------------------------------------------------------------------------------------------
db_file_name_convert
(NULL)
log_file_name_convert
(NULL)
log_archive_dest_1
LOCATION=+FRA1
log_archive_dest_2
(NULL)
log_archive_dest_state_1
enable
log_archive_dest_state_2
enable
fal_client
(NULL)
fal_server
(NULL)
log_archive_config
(NULL)
log_archive_format
%t_%s_%r.ARC
log_archive_max_processes
4
standby_file_management
MANUAL
remote_login_passwordfile
EXCLUSIVE
db_name
ORA12R1
db_unique_name
ORA12R1
--> 이 상황이 ADG 설정 전 Primary DB의 파라미터
현황임
15 rows selected.
Elapsed: 00:00:00.00
2-3-3. Primary DB에 ADG 관련 파라미터 설정
[ol7ora12r11]<SYS@ORA12R11>$
ALTERSYSTEMSETLOG_ARCHIVE_CONFIG='DG_CONFIG=(ORA12R1,ORA12R1_STB)' SCOPE=BOTH
SID='*';
-->
db_unique_name을 지정함
System altered.
Elapsed: 00:00:00.03
[ol7ora12r11]<SYS@ORA12R11>$ ALTERSYSTEMSETLOG_ARCHIVE_DEST_1='LOCATION=+FRA1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA12R1' SCOPE=BOTH SID='*';
System altered.
Elapsed: 00:00:00.01
[ol7ora12r11]<SYS@ORA12R11>$ ALTERSYSTEMSETLOG_ARCHIVE_DEST_2='SERVICE=TNS_ORA12R1_STB LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA12R1_STB REOPEN=5' SCOPE=BOTH SID='*';
System altered.
Elapsed: 00:00:00.01
[ol7ora12r11]<SYS@ORA12R11>$ ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH SID='*';
System altered.
Elapsed: 00:00:00.01
[ol7ora12r11]<SYS@ORA12R11>$ ALTER SYSTEM SET
LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH SID='*';
System altered.
Elapsed: 00:00:00.02
[ol7ora12r11]<SYS@ORA12R11>$ ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4 SCOPE=BOTH SID='*';
System altered.
Elapsed: 00:00:00.01
[ol7ora12r11]<SYS@ORA12R11>$ ALTER SYSTEM SET FAL_SERVER=TNS_ORA12R1_STB SCOPE=BOTH SID='*';
System altered.
Elapsed: 00:00:00.01
[ol7ora12r11]<SYS@ORA12R11>$ ALTER SYSTEM SET
FAL_CLIENT=TNS_ORA12R1 SCOPE=BOTH SID='*';
System altered.
Elapsed: 00:00:00.00
[ol7ora12r11]<SYS@ORA12R11>$ ALTER SYSTEM SET DB_FILE_NAME_CONVERT='ORA12R1_STB','ORA12R1' SCOPE=SPFILE SID='*';
System altered.
Elapsed: 00:00:00.01
[ol7ora12r11]<SYS@ORA12R11>$ ALTER SYSTEM SET
LOG_FILE_NAME_CONVERT='ORA12R1_STB','ORA12R1' SCOPE=SPFILE
SID='*';
System altered.
Elapsed: 00:00:00.00
[ol7ora12r11]<SYS@ORA12R11>$ ALTER SYSTEM SET
STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH SID= '*';
System altered.
Elapsed: 00:00:00.00
--파라미터 확인
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ alias
cdos
alias cdos='cd
/home/oracle/oracle_scripts'
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$
cdos
[ORA12R11:oracle@ol7ora12r11][/home/oracle/oracle_scripts]$
ss
SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 18 20:33:04 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production
[ol7ora12r11]<SYS@ORA12R11>$ @adg_par
NAME
VALUE
--------------------------------------------------
------------------------------------------------------------------------------------------
db_file_name_convert
(NULL)
log_file_name_convert
(NULL)
log_archive_dest_1
LOCATION=+FRA1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=ORA12R1
log_archive_dest_2
SERVICE=TNS_ORA12R1_STB LGWR ASYNC NOAFFIRM
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA12R1_STB
REOPEN=5
log_archive_dest_state_1
ENABLE
log_archive_dest_state_2
ENABLE
fal_client
TNS_ORA12R1
fal_server
TNS_ORA12R1_STB
log_archive_config
DG_CONFIG=(ORA12R1,ORA12R1_STB)
log_archive_format
%t_%s_%r.ARC
log_archive_max_processes
4
standby_file_management
AUTO
remote_login_passwordfile
EXCLUSIVE
db_name
ORA12R1
db_unique_name
ORA12R1
15 rows selected.
Elapsed: 00:00:00.01
2-3-4.
Primary DB에 Standby redo log file 추가
[ol7ora12r11]<SYS@ORA12R11>$ host vi
/home/oracle/oracle_scripts/rdl.sql
[ol7ora12r11]<SYS@ORA12R11>$ host cat
/home/oracle/oracle_scripts/rdl.sql
col group# for 999
col thread# for
999
col member for a50
col STATUS for a10
col TYPE for a10
col MB
for 999,999,999
col STATUS for a15
SELECT
A.GROUP#
, B.THREAD#
,
B.SEQUENCE#
, A.MEMBER
,
A.STATUS
, A.TYPE
,
B.BYTES/1024/1024 AS MB
, B.STATUS
FROM
V$LOGFILE A
, V$LOG B
WHERE A.GROUP# =
B.GROUP#
ORDER BY 1
;
[ol7ora12r11]<SYS@ORA12R11>$ host
pwd
/home/oracle/oracle_scripts
[ol7ora12r11]<SYS@ORA12R11>$ @rdl
GROUP#
THREAD# SEQUENCE#
MEMBER
STATUS
TYPE
MB STATUS
------ ------- ----------
-------------------------------------------------- --------------- ----------
------------ ---------------
1
1 10
+DATA1/ORA12R1/ONLINELOG/group_1.274.1211843917
(NULL)
ONLINE
200 INACTIVE
1
1 10
+FRA1/ORA12R1/ONLINELOG/group_1.299.1211843917
(NULL)
ONLINE
200 INACTIVE
2
1 11
+FRA1/ORA12R1/ONLINELOG/group_2.258.1211843917
(NULL)
ONLINE
200 CURRENT
2
1 11
+DATA1/ORA12R1/ONLINELOG/group_2.278.1211843917
(NULL)
ONLINE
200 CURRENT
3
1 9
+DATA1/ORA12R1/ONLINELOG/group_3.266.1211843917
(NULL)
ONLINE
200 INACTIVE
3
1 9
+FRA1/ORA12R1/ONLINELOG/group_3.257.1211843917
(NULL)
ONLINE
200 INACTIVE
4
2 10
+FRA1/ORA12R1/ONLINELOG/group_4.259.1211843917
(NULL)
ONLINE
200 INACTIVE
4
2 10
+DATA1/ORA12R1/ONLINELOG/group_4.267.1211843917
(NULL)
ONLINE
200 INACTIVE
5
2 11
+FRA1/ORA12R1/ONLINELOG/group_5.256.1211843917
(NULL)
ONLINE
200 CURRENT
5
2 11
+DATA1/ORA12R1/ONLINELOG/group_5.277.1211843917
(NULL)
ONLINE
200 CURRENT
6
2 9
+FRA1/ORA12R1/ONLINELOG/group_6.296.1211843917
(NULL)
ONLINE
200 INACTIVE
6
2 9
+DATA1/ORA12R1/ONLINELOG/group_6.272.1211843917
(NULL)
ONLINE
200 INACTIVE
12 rows selected.
Elapsed: 00:00:00.01
[ol7ora12r11]<SYS@ORA12R11>$ host vi /home/oracle/oracle_scripts/stb_rdl.sql
[ol7ora12r11]<SYS@ORA12R11>$ host cat
/home/oracle/oracle_scripts/stb_rdl.sql
COLUMN DUMMY FORMAT 999
COLUMN
GROUP# FORMAT 9999
COLUMN THREAD# FORMAT 9999
COLUMN SEQUENCE# FORMAT
9999999
COLUMN ARCHIVED FORMAT A8
COLUMN STATUS FORMAT A12
COLUMN
MEMBER FORMAT A60 WORD_WRAPPED
SELECT 1 AS
DUMMY,
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.STATUS AS
LOG_STATUS,
b.TYPE,
b.MEMBER
FROM
V$STANDBY_LOG a, v$logfile b
WHERE a.group# = b.group#
ORDER
BY a.GROUP#;
[ol7ora12r11]<SYS@ORA12R11>$ @stb_rdl
no rows selected
Elapsed: 00:00:00.01
[ol7ora12r11]<SYS@ORA12R11>$
ALTER DATABASE ADD STANDBY LOGFILE
THREAD 1 group 7('+DATA1','+FRA1') SIZE 200M;
ALTER DATABASE ADD
STANDBY LOGFILE THREAD 1 group 8('+DATA1','+FRA1') SIZE 200M;
ALTER
DATABASE ADD STANDBY LOGFILE THREAD 1 group 9('+DATA1','+FRA1') SIZE
200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 10('+DATA1','+FRA1')
SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 group
11('+DATA1','+FRA1') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
group 12('+DATA1','+FRA1') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE
THREAD 2 group 13('+DATA1','+FRA1') SIZE 200M;
ALTER DATABASE ADD STANDBY
LOGFILE THREAD 2 group 14('+DATA1','+FRA1') SIZE 200M;
--> 스탠바이 로그의 그룹의 개수는 일반 redo log보다 1개 더 많게해야함.
중요!!
[ol7ora12r11]<SYS@ORA12R11>$ @stb_rdl
DUMMY
GROUP# THREAD# SEQUENCE# ARCHIVED STATUS
LOG_STATUS
TYPE MEMBER
----- ------ -------
--------- -------- ------------ --------------------- ----------
------------------------------------------------------------
1 7
1 0
YES UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1/ONLINELOG/group_7.273.1212180303
1 7
1 0
YES UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1/ONLINELOG/group_7.262.1212180303
1 8
1 0
YES UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1/ONLINELOG/group_8.261.1212180305
1 8
1 0
YES UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1/ONLINELOG/group_8.301.1212180305
1 9
1 0
YES UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1/ONLINELOG/group_9.259.1212180305
1 9
1 0
YES UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1/ONLINELOG/group_9.303.1212180305
1 10
1 0
YES UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1/ONLINELOG/group_10.258.1212180305
1 10
1 0
YES UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1/ONLINELOG/group_10.287.1212180305
1 11
2 0
YES UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1/ONLINELOG/group_11.257.1212180305
1 11
2 0
YES UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1/ONLINELOG/group_11.300.1212180305
1 12
2 0
YES UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1/ONLINELOG/group_12.281.1212180305
1 12
2 0
YES UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1/ONLINELOG/group_12.292.1212180305
1 13
2 0
YES UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1/ONLINELOG/group_13.282.1212180305
1 13
2 0
YES UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1/ONLINELOG/group_13.263.1212180307
1 14
2 0
YES UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1/ONLINELOG/group_14.283.1212180309
1 14
2 0
YES UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1/ONLINELOG/group_14.294.1212180309
16 rows selected.
Elapsed: 00:00:00.01
2-3-4.
Primary DB 풀 백업
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ mkdir -pv
/home/oracle/BACKUP_FOR_ADG/autobackup
mkdir: created directory
‘/home/oracle/BACKUP_FOR_ADG/autobackup’
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$
vi .bash_profile
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ cat
.bash_profile | grep cdbfa
alias cdbfa='cd
/home/oracle/BACKUP_FOR_ADG'
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ .
./.bash_profile
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$
cdbfa
[ORA12R11:oracle@ol7ora12r11][/home/oracle/BACKUP_FOR_ADG]$ alias
rt
alias rt='rman target /'
[ORA12R11:oracle@ol7ora12r11][/home/oracle/BACKUP_FOR_ADG]$
rt
Recovery Manager: Release 12.2.0.1.0 - Production on Thu Sep 18 20:50:09 2025
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA12R1 (DBID=270370777)
RMAN>
CONFIGURE
CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE
TYPE DISK TO '/home/oracle/BACKUP_FOR_ADG/autobackup/%F';
run
{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
ALLOCATE CHANNEL c2
DEVICE TYPE DISK;
ALLOCATE CHANNEL c3 DEVICE TYPE
DISK;
ALLOCATE CHANNEL c4 DEVICE TYPE DISK;
sql 'alter system
checkpoint';
sql 'alter system archive log
current';
crosscheck backupset;
crosscheck
backup;
crosscheck copy;
crosscheck archivelog
all;
BACKUP INCREMENTAL LEVEL 0 AS COMPRESSED BACKUPSET FORMAT
'/home/oracle/BACKUP_FOR_ADG/%d_L0_%T_%U.bkp' DATABASE;
delete noprompt
obsolete;
delete noprompt expired backup;
sql 'alter system
archive log current';
BACKUP AS COMPRESSED BACKUPSET FORMAT
'/home/oracle/BACKUP_FOR_ADG/arch_%d_%T_%U.bkp' ARCHIVELOG ALL delete
input;
delete backup of archivelog all completed before
'SYSDATE-14';
BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT
'/home/oracle/BACKUP_FOR_ADG/STB_CTL_%U_%T';
RELEASE CHANNEL
c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE
CHANNEL c4;
}
RMAN> list backup;
List of
Backup Sets
===================
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
157 Incr 0
3.05M
DISK 00:00:00
2025-09-18 21:00:12
BP Key:
157 Status: AVAILABLE Compressed: YES Tag:
TAG20250918T210012
Piece Name:
/home/oracle/BACKUP_FOR_ADG/ORA12R1_L0_20250918_5e440qms_1_1.bkp
List
of Datafiles in backup set 157
File LV Type Ckp SCN
Ckp Time Abs
Fuz SCN Sparse Name
---- -- ---- ---------- -------------------
----------- ------ ----
4 0 Incr
4314784 2025-09-18
21:00:12
NO +DATA1/ORA12R1/DATAFILE/undotbs1.271.1211839551
5 0 Incr 4314784 2025-09-18
21:00:12
NO
+DATA1/ORA12R1/DATAFILE/undotbs2.264.1211839551
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
158 Incr 0
14.15M DISK
00:00:01 2025-09-18
21:00:13
BP Key: 158
Status: AVAILABLE Compressed: YES Tag:
TAG20250918T210012
Piece Name:
/home/oracle/BACKUP_FOR_ADG/ORA12R1_L0_20250918_5f440qms_1_1.bkp
List
of Datafiles in backup set 158
File LV Type Ckp SCN
Ckp Time Abs
Fuz SCN Sparse Name
---- -- ---- ---------- -------------------
----------- ------ ----
7 0 Incr
4314787 2025-09-18
21:00:12
NO +DATA1/ORA12R1/DATAFILE/users.260.1211839555
8 0 Incr 4314787 2025-09-18
21:00:12
NO
+DATA1/ORA12R1/DATAFILE/tuner_idx1.263.1211839555
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
159 Incr 0
243.85M DISK
00:00:12 2025-09-18
21:00:24
BP Key: 159
Status: AVAILABLE Compressed: YES Tag:
TAG20250918T210012
Piece Name:
/home/oracle/BACKUP_FOR_ADG/ORA12R1_L0_20250918_5c440qms_1_1.bkp
List
of Datafiles in backup set 159
File LV Type Ckp SCN
Ckp Time Abs
Fuz SCN Sparse Name
---- -- ---- ---------- -------------------
----------- ------ ----
1 0 Incr
4314782 2025-09-18
21:00:12
NO +DATA1/ORA12R1/DATAFILE/system.269.1211839557
2 0 Incr 4314782 2025-09-18
21:00:12
NO
+DATA1/ORA12R1/DATAFILE/tuner_data1.268.1211839557
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
160 Incr 0
367.87M DISK
00:00:20 2025-09-18
21:00:32
BP Key: 160
Status: AVAILABLE Compressed: YES Tag:
TAG20250918T210012
Piece Name:
/home/oracle/BACKUP_FOR_ADG/ORA12R1_L0_20250918_5d440qms_1_1.bkp
List
of Datafiles in backup set 160
File LV Type Ckp SCN
Ckp Time Abs
Fuz SCN Sparse Name
---- -- ---- ---------- -------------------
----------- ------ ----
3 0 Incr
4314783 2025-09-18
21:00:12
NO
+DATA1/ORA12R1/DATAFILE/sysaux.270.1211839583
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
161 Full
19.28M DISK
00:00:00 2025-09-18
21:00:37
BP Key: 161
Status: AVAILABLE Compressed: NO Tag:
TAG20250918T210037
Piece Name:
/home/oracle/BACKUP_FOR_ADG/autobackup/c-270370777-20250918-05
SPFILE
Included: Modification time: 2025-09-18 20:32:21
SPFILE
db_unique_name: ORA12R1
Control File Included: Ckp SCN:
4314846 Ckp time: 2025-09-18
21:00:37
BS
Key Size Device Type Elapsed Time
Completion Time
------- ---------- ----------- ------------
-------------------
162
2.60M
DISK 00:00:00
2025-09-18 21:00:44
BP Key:
162 Status: AVAILABLE Compressed: YES Tag:
TAG20250918T210044
Piece Name:
/home/oracle/BACKUP_FOR_ADG/arch_ORA12R1_20250918_5k440qns_1_1.bkp
List
of Archived Logs in backup set 162
Thrd Seq
Low SCN Low
Time Next
SCN Next Time
---- ------- ---------- -------------------
---------- ---------
1
13 4312141 2025-09-18 20:53:58
4312165 2025-09-18 20:54:01
1
14 4312165 2025-09-18 20:54:01
4314726 2025-09-18 21:00:10
2
12 4311964 2025-09-18 20:53:29
4312148 2025-09-18 20:53:59
2
13 4312148 2025-09-18 20:53:59
4312176 2025-09-18 20:54:02
2
14 4312176 2025-09-18 20:54:02
4314733 2025-09-18 21:00:11
BS
Key Size Device Type Elapsed Time
Completion Time
------- ---------- ----------- ------------
-------------------
163
7.46M
DISK 00:00:00
2025-09-18 21:00:44
BP Key:
163 Status: AVAILABLE Compressed: YES Tag:
TAG20250918T210044
Piece Name:
/home/oracle/BACKUP_FOR_ADG/arch_ORA12R1_20250918_5j440qns_1_1.bkp
List
of Archived Logs in backup set 163
Thrd Seq
Low SCN Low
Time Next
SCN Next Time
---- ------- ---------- -------------------
---------- ---------
1
10 4301823 2025-09-18 20:12:36
4302698 2025-09-18 20:18:24
1
11 4302698 2025-09-18 20:18:24
4311957 2025-09-18 20:53:28
1
12 4311957 2025-09-18 20:53:28
4312141 2025-09-18 20:53:58
2
10 4301820 2025-09-18 20:12:34
4302707 2025-09-18 20:18:25
2
11 4302707 2025-09-18 20:18:25
4311964 2025-09-18 20:53:29
BS
Key Size Device Type Elapsed Time
Completion Time
------- ---------- ----------- ------------
-------------------
164
46.50K DISK
00:00:00 2025-09-18
21:00:45
BP Key: 164
Status: AVAILABLE Compressed: YES Tag:
TAG20250918T210044
Piece Name:
/home/oracle/BACKUP_FOR_ADG/arch_ORA12R1_20250918_5l440qnt_1_1.bkp
List
of Archived Logs in backup set 164
Thrd Seq
Low SCN Low
Time Next
SCN Next Time
---- ------- ---------- -------------------
---------- ---------
1
15 4314726 2025-09-18 21:00:10
4314883 2025-09-18 21:00:40
1
16 4314883 2025-09-18 21:00:40
4314907 2025-09-18 21:00:43
2
15 4314733 2025-09-18 21:00:11
4314890 2025-09-18 21:00:41
2
16 4314890 2025-09-18 21:00:41
4314912 2025-09-18 21:00:44
BS
Key Size Device Type Elapsed Time
Completion Time
------- ---------- ----------- ------------
-------------------
165
83.90M DISK
00:00:04 2025-09-18
21:00:48
BP Key: 165
Status: AVAILABLE Compressed: YES Tag:
TAG20250918T210044
Piece Name:
/home/oracle/BACKUP_FOR_ADG/arch_ORA12R1_20250918_5i440qns_1_1.bkp
List
of Archived Logs in backup set 165
Thrd Seq
Low SCN Low
Time Next
SCN Next Time
---- ------- ---------- -------------------
---------- ---------
1
9 4225341 2025-09-18
11:30:03 4301823 2025-09-18 20:12:36
2 9
4226976 2025-09-18 11:40:04 4301820
2025-09-18 20:12:34
BS
Key Size Device Type Elapsed Time
Completion Time
------- ---------- ----------- ------------
-------------------
166
90.21M DISK
00:00:04 2025-09-18
21:00:48
BP Key: 166
Status: AVAILABLE Compressed: YES Tag:
TAG20250918T210044
Piece Name:
/home/oracle/BACKUP_FOR_ADG/arch_ORA12R1_20250918_5h440qns_1_1.bkp
List
of Archived Logs in backup set 166
Thrd Seq
Low SCN Low
Time Next
SCN Next Time
---- ------- ---------- -------------------
---------- ---------
1
8 4142732 2025-09-18
02:21:06 4225341 2025-09-18 11:30:03
2 8
4142729 2025-09-18 02:21:06 4226976
2025-09-18 11:40:04
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
167 Full
19.28M DISK
00:00:00 2025-09-18
21:00:48
BP Key: 167
Status: AVAILABLE Compressed: NO Tag:
TAG20250918T210048
Piece Name:
/home/oracle/BACKUP_FOR_ADG/autobackup/c-270370777-20250918-06
SPFILE
Included: Modification time: 2025-09-18 20:32:21
SPFILE
db_unique_name: ORA12R1
Control File Included: Ckp SCN:
4314972 Ckp time: 2025-09-18
21:00:48
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
168 Full
19.25M DISK
00:00:01 2025-09-18
21:00:51
BP Key: 168
Status: AVAILABLE Compressed: NO Tag:
TAG20250918T210050
Piece Name:
/home/oracle/BACKUP_FOR_ADG/STB_CTL_5n440qo2_1_1_20250918
Standby
Control File Included: Ckp SCN: 4314995 Ckp time:
2025-09-18 21:00:50
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
169 Full
19.28M DISK
00:00:00 2025-09-18
21:00:52
BP Key: 169
Status: AVAILABLE Compressed: NO Tag:
TAG20250918T210052
Piece Name:
/home/oracle/BACKUP_FOR_ADG/autobackup/c-270370777-20250918-07
SPFILE
Included: Modification time: 2025-09-18 20:32:21
SPFILE
db_unique_name: ORA12R1
Control File Included: Ckp SCN:
4315007 Ckp time: 2025-09-18
21:00:52
[ol7ora12r11]<SYS@ORA12R11>$ create pfile='/home/oracle/BACKUP_FOR_ADG/initORA12R1.ora' from spfile;
File created.
Elapsed: 00:00:00.01
[ORA12R11:oracle@ol7ora12r11][/home/oracle/BACKUP_FOR_ADG]$ srvctl config
database -db ora12r1
Database unique name:
ORA12R1
Database name: ORA12R1
Oracle home:
/u01/app/oracle/product/12c/db_1
Oracle user: oracle
Spfile:
+DATA1/ORA12R1/PARAMETERFILE/spfile.276.1211378811
Password file:
+DATA1/ORA12R1/PASSWORD/pwdora12r1.262.1211378615
Domain:
Start options:
open
Stop options: immediate
Database role: PRIMARY
Management policy:
AUTOMATIC
Server pools:
Disk Groups: FRA1,DATA1
Mount point
paths:
Services:
Type: RAC
Start concurrency:
Stop
concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances:
ORA12R11,ORA12R12
Configured nodes: ol7ora12r11,ol7ora12r12
CSS critical:
no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network
number for database services:
Database is administrator
managed
[+ASM1:grid@ol7ora12r11][/home/grid]$ asmcmd pwcopy
+DATA1/ORA12R1/PASSWORD/pwdora12r1.262.1211378615
/tmp/orapwORA12R1
copying
+DATA1/ORA12R1/PASSWORD/pwdora12r1.262.1211378615 ->
/tmp/orapwORA12R1
[+ASM1:grid@ol7ora12r11][/home/grid]$
chmod 664 /tmp/orapwORA12R1
[ORA12R11:oracle@ol7ora12r11][/home/oracle/BACKUP_FOR_ADG]$ cp /tmp/orapwORA12R1 /home/oracle/BACKUP_FOR_ADG
[ORA12R11:oracle@ol7ora12r11][/home/oracle/BACKUP_FOR_ADG]$ ls -alRt
/home/oracle/BACKUP_FOR_ADG
/home/oracle/BACKUP_FOR_ADG:
total 852436
drwxr-xr-x. 3
oracle oinstall 4096 Sep 18 21:44
.
-rw-r--r--. 1 oracle oinstall 3584 Sep
18 21:44 orapwORA12R1
-rw-r--r--. 1 oracle
asmadmin 2748 Sep 18 21:06
initORA12R1.ora
drwxr-xr-x. 2 oracle
oinstall 99 Sep 18 21:00
autobackup
-rw-r-----. 1 oracle asmadmin 20201472 Sep 18 21:00
STB_CTL_5n440qo2_1_1_20250918
-rw-r-----. 1 oracle asmadmin
94590464 Sep 18 21:00
arch_ORA12R1_20250918_5h440qns_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 87975936 Sep 18 21:00
arch_ORA12R1_20250918_5i440qns_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 48128 Sep 18 21:00
arch_ORA12R1_20250918_5l440qnt_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 7827456 Sep 18 21:00
arch_ORA12R1_20250918_5j440qns_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 2730496 Sep 18 21:00
arch_ORA12R1_20250918_5k440qns_1_1.bkp
-rw-r-----. 1 oracle asmadmin
385744896 Sep 18 21:00 ORA12R1_L0_20250918_5d440qms_1_1.bkp
-rw-r-----.
1 oracle asmadmin 255705088 Sep 18 21:00
ORA12R1_L0_20250918_5c440qms_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 14843904 Sep 18 21:00
ORA12R1_L0_20250918_5f440qms_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 3203072 Sep 18 21:00
ORA12R1_L0_20250918_5e440qms_1_1.bkp
drwx------. 10 oracle
oinstall 4096 Sep 18 20:49 ..
/home/oracle/BACKUP_FOR_ADG/autobackup:
total 59284
drwxr-xr-x. 3
oracle oinstall 4096 Sep 18 21:44 ..
-rw-r-----. 1
oracle asmadmin 20234240 Sep 18 21:00 c-270370777-20250918-07
drwxr-xr-x. 2
oracle oinstall 99 Sep 18 21:00
.
-rw-r-----. 1 oracle asmadmin 20234240 Sep 18 21:00
c-270370777-20250918-06
-rw-r-----. 1 oracle asmadmin 20234240 Sep 18 21:00
c-270370777-20250918-05
3.
Standby DB 설정
3-1. Primary 서버에서 백업본을 가져오기
[ORA12S1:oracle@ol7ora12s1][/home/oracle]$ mkdir -pv
/home/oracle/BACKUP_FOR_ADG
mkdir: created directory
‘/home/oracle/BACKUP_FOR_ADG’
[ORA12S1:oracle@ol7ora12s1][/home/oracle]$ vi
.bash_profile
[ORA12S1:oracle@ol7ora12s1][/home/oracle]$ cat .bash_profile |
grep cdbfa
alias cdbfa='cd
/home/oracle/BACKUP_FOR_ADG'
[ORA12S1:oracle@ol7ora12s1][/home/oracle]$ .
./.bash_profile
[ORA12S1:oracle@ol7ora12s1][/home/oracle]$
cdbfa
[ORA12S1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$ scp -r
ol7ora12r11:/home/oracle/BACKUP_FOR_ADG/*
/home/oracle/BACKUP_FOR_ADG
*/
The authenticity of host
'ol7ora12r11 (192.168.240.11)' can't be established.
ECDSA key fingerprint is
SHA256:3pgck5e3N7EJhHeyNeY0dDlS/9LplMyuZM2/XeARoiI.
ECDSA key fingerprint is
MD5:17:48:1b:ea:b0:16:ce:c3:01:27:64:76:da:04:44:34.
Are you sure you want to
continue connecting (yes/no)? yes
Warning: Permanently added
'ol7ora12r11,192.168.240.11' (ECDSA) to the list of known
hosts.
oracle@ol7ora12r11's
password:
arch_ORA12R1_20250918_5h440qns_1_1.bkp
100% 90MB 242.5MB/s
00:00
arch_ORA12R1_20250918_5i440qns_1_1.bkp
100% 84MB 281.3MB/s
00:00
arch_ORA12R1_20250918_5j440qns_1_1.bkp
100% 7644KB 226.5MB/s
00:00
arch_ORA12R1_20250918_5k440qns_1_1.bkp
100% 2667KB 227.5MB/s
00:00
arch_ORA12R1_20250918_5l440qnt_1_1.bkp
100% 47KB 43.5MB/s
00:00
c-270370777-20250918-05
100% 19MB 236.4MB/s
00:00
c-270370777-20250918-06
100% 19MB 219.1MB/s
00:00
c-270370777-20250918-07
100% 19MB 236.5MB/s
00:00
initORA12R1.ora
100% 2748 798.7KB/s
00:00
ORA12R1_L0_20250918_5c440qms_1_1.bkp
100% 244MB 234.9MB/s
00:01
ORA12R1_L0_20250918_5d440qms_1_1.bkp
100% 368MB 234.7MB/s
00:01
ORA12R1_L0_20250918_5e440qms_1_1.bkp
100% 3128KB 188.6MB/s
00:00
ORA12R1_L0_20250918_5f440qms_1_1.bkp
100% 14MB 219.8MB/s
00:00
orapwORA12R1
100% 3584 940.8KB/s
00:00
STB_CTL_5n440qo2_1_1_20250918
100% 19MB 234.2MB/s 00:00
[ORA12S1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$
ls
arch_ORA12R1_20250918_5h440qns_1_1.bkp
arch_ORA12R1_20250918_5k440qns_1_1.bkp
initORA12R1.ora
ORA12R1_L0_20250918_5e440qms_1_1.bkp
STB_CTL_5n440qo2_1_1_20250918
arch_ORA12R1_20250918_5i440qns_1_1.bkp
arch_ORA12R1_20250918_5l440qnt_1_1.bkp
ORA12R1_L0_20250918_5c440qms_1_1.bkp
ORA12R1_L0_20250918_5f440qms_1_1.bkp
arch_ORA12R1_20250918_5j440qns_1_1.bkp
autobackup
ORA12R1_L0_20250918_5d440qms_1_1.bkp
orapwORA12R1
[ORA12S1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$ ls -l
./autobackup/
total 59280
-rw-r-----. 1 oracle oinstall 20234240 Sep 18 21:53
c-270370777-20250918-05
-rw-r-----. 1 oracle oinstall 20234240 Sep 18 21:53
c-270370777-20250918-06
-rw-r-----. 1 oracle oinstall 20234240 Sep 18 21:53
c-270370777-20250918-07
3-2. Standby DB내 바이너리 권한 확인
[ORA12S1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$ ls -al
$ORACLE_HOME/bin | egrep
"extjob$|jssu$|oracle$|oradism"
-rwsr-x---. 1 root
oinstall 2241432 Sep 13 15:47 extjob
-rwsr-x---. 1
root oinstall 2252256 Sep 13 14:32
jssu
-rwsr-s--x. 1 oracle asmadmin 408794904 Sep 13 15:47
oracle
-rwsr-x---. 1 root oinstall
95844 Jan 26 2017 oradism
--> 만약 소유자/그룹 및 권한이 위와 같지
않다면 맞춰줘야함
--> oracle은 oracle:asmadmin에 6751
--> extjob, jssu, oradism 은 root:oinstall에
4750
[ORA12S1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$
ls -al $GRID_HOME/bin | egrep "extjob$|jssu$|oracle$|oradism"
-rwsr-x---. 1 root
oinstall 2241432 Sep 13 15:36 extjob
-rwsr-x---. 1 root oinstall 2252256 Sep
13 13:41 jssu
-rwsr-s--x. 1 grid oinstall
373530016 Sep 13 15:36 oracle
-rwsr-x---. 1 root
oinstall 95844 Jan 26 2017 oradism
--> 만약 소유자/그룹 및 권한이 위와 같지
않다면 맞춰줘야함
--> oracle은 grid:oinstall 6751
--> extjob, jssu, oradism 은 root:oinstall에
4750
3-3. Standby DB내 필수 디렉토리 생성
[ORA12S1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$
ls -l /u01/app/oracle | grep admin
drwxrwx---.
4 grid oinstall 33 Sep 13 14:52 admin
--> 해당 디렉토리(/u01/app/oracle/admin)의 권한이 770인 것을
확인함
[ORA12S1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$
mkdir -pv /u01/app/oracle/admin/ORA12R1_STB/adump
mkdir: created
directory ‘/u01/app/oracle/admin/ORA12R1_STB’
mkdir:
created directory ‘/u01/app/oracle/admin/ORA12R1_STB/adump’
3-4. oracle os user ssh 로그인 설정
[ORA12S1:oracle@ol7ora12s1][/home/oracle]$ alias
cdoss
alias cdoss='cd
/home/oracle/os_scripts'
[ORA12S1:oracle@ol7ora12s1][/home/oracle]$ mkdir -pv
/home/oracle/os_scripts
[ORA12S1:oracle@ol7ora12s1][/home/oracle]$
cdoss
[ORA12S1:oracle@ol7ora12s1][/home/oracle/os_scripts]$ vi
/home/oracle/os_scripts/oenv.sh
[ORA12S1:oracle@ol7ora12s1][/home/oracle/os_scripts]$ cat
/home/oracle/os_scripts/oenv.sh
#!/bin/bash
echo "접속할
데이터베이스(DB_NAME)를 선택하세요:"
echo "1) ORA12S1"
echo "2) ORA12R1"
echo "3)
RESERVED"
if [ "$#" -eq 0 ];
then
read -e -r -p "번호를 입력하세요
(1,2,3): " choice
else
choice="$1"
fi
case "$choice"
in
1) SID="ORA12S1" ;;
2) SID="ORA12R1" ;;
3) SID="RESERVED" ;;
*)
echo "잘못된 입력입니다.
ORA12S1 DB로 적용합니다."
SID="ORA12S1"
;;
esac
export ORACLE_SID="$SID"
echo "현재
ORACLE_SID: $ORACLE_SID"
if [ -z "$ORACLE_SID" ];
then
echo "ORACLE_SID 환경변수가 설정되어 있지
않습니다. (예: export ORACLE_SID=ORA12S1)"
exit 1
fi
case "$ORACLE_SID"
in
ORA12S1)
echo "ORA12S1 환경
설정 적용 중..."
export ORACLE_UNQNAME=ORA12S1
export
ORACLE_UNQNAME_LOWER=ORA12S1
export
ORACLE_DBNAME=ORA12S1
export
ORACLE_DBNAME_LOWER=ora12s1
export
ORACLE_SID=ORA12S1
export
ORACLE_SID_LOWER=ora12s1
;;
ORA12R1)
echo "ORA12R1 환경
설정 적용 중..."
export ORACLE_UNQNAME=ORA12R1_STB
export
ORACLE_UNQNAME_LOWER=ora12r1_stb
export
ORACLE_DBNAME=ORA12R1
export
ORACLE_DBNAME_LOWER=ora12r1
export
ORACLE_SID=ORA12R1
export
ORACLE_SID_LOWER=ora12r1
;;
RESERVED)
echo "RESERVED
환경 설정 적용 중..."
export
ORACLE_UNQNAME=RESERVED
export
ORACLE_UNQNAME_LOWER=reserved
export
ORACLE_DBNAME=RESERVED
export
ORACLE_DBNAME_LOWER=reserved
export
ORACLE_SID=RESERVED
export
ORACLE_SID_LOWER=reserved
;;
*)
echo "알 수 없는
ORACLE_SID: $ORACLE_SID"
exit 1
;;
esac
echo "환경 설정 완료 : 현재 ORACLE_SID = $ORACLE_SID"
[ORA12S1:oracle@ol7ora12s1][/home/oracle/os_scripts]$ chmod 775 /home/oracle/os_scripts/oenv.sh
[ORA12S1:oracle@ol7ora12s1][/home/oracle/os_scripts]$ vi
~oracle/.bash_profile
[ORA12S1:oracle@ol7ora12s1][/home/oracle/os_scripts]$ cat
~oracle/.bash_profile
#
.bash_profile
# Get the aliases
and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
stty erase $(tput kbs)
.
/home/oracle/os_scripts/oenv.sh
--> 맨
윗부분에 추가
export PATH
export TMP=/tmp
export
TMPDIR=$TMP
export EDITOR=vi
export ORACLE_HOSTNAME=ol7ora12s1
#export ORACLE_UNQNAME=ORA12S1 --> 주석
처리
#export ORACLE_UNQNAME_LOWER=ora12s1
--> 주석
처리
export
ORACLE_BASE=/u01/app/oracle
export
GRID_HOME=/u01/app/12c/grid
export
DB_HOME=$ORACLE_BASE/product/12c/db_1
export
ORACLE_HOME=$DB_HOME
#export
ORACLE_DBNAME=ORA12S1 --> 주석
처리
#export
ORACLE_DBNAME_LOWER=ORA12S1 --> 주석
처리
#export
ORACLE_SID=ORA12S1 --> 주석
처리
#export
ORACLE_SID_LOWER=ora12s1 --> 주석
처리
export
ORACLE_TERM=xterm
export BASE_PATH=/usr/sbin:$PATH
export
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$GRID_HOME/bin:$BASE_PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_LANG=AMERICAN_KOREA.AL32UTF8
export PS1='[$ORACLE_SID:\u@\h][$PWD]$ '
alias ss='rlwrap sqlplus "/as sysdba"'
alias st='rlwrap sqlplus tuner/oracle'
alias cdt='cd $ORACLE_HOME/network/admin'
alias cdb='cd $ORACLE_BASE'
alias
cdo='cd $ORACLE_HOME'
alias cdod='cd
$ORACLE_HOME/dbs'
alias alog='tail -f
$ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
alias valog='vi -R
$ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
alias llog='tail -f
$ORACLE_BASE/diag/tnslsnr/`hostname`/listener/trace/listener.log'
alias vllog='vi -R
$ORACLE_BASE/diag/tnslsnr/`hostname`/listener/trace/listener.log'
alias cdord='cd $ORACLE_BASE/oradata/$ORACLE_SID'
alias csrt='crsctl stat res -t'
alias cdrba='cd
/home/oracle/rman_backup/$ORACLE_DBNAME/ADG'
alias
rt='rman target /'
alias cdos='cd
/home/oracle/oracle_scripts'
alias cdoss='cd
/home/oracle/os_scripts'
alias cdrb='cd
/home/oracle/rman_backup/$ORACLE_DBNAME'
alias cdrba='cd
/home/oracle/rman_backup/$ORACLE_DBNAME/ADG'
alias
cdrbl='cd /home/oracle/os_scripts/rman_log'
alias
cdbfa='cd /home/oracle/BACKUP_FOR_ADG'
set -o vi
stty erase ^H
stty erase
^?
alias oenv='source /home/oracle/os_scripts/oenv.sh'
--> 맨
아래에 추가
--새로운 ssh 창으로 접속 (oracle os
user로 접속)
접속할 데이터베이스(DB_NAME)를
선택하세요:
1) ORA12S1
2) ORA12R1
3) RESERVED
번호를 입력하세요 (1,2,3):
2
현재 ORACLE_SID: ORA12R1
ORA12R1 환경
설정 적용 중...
환경 설정 완료 : 현재 ORACLE_SID = ORA12R1
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$ echo
$ORACLE_UNQNAME
ORA12R1_STB
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$ echo
$ORACLE_UNQNAME_LOWER
ora12r1_stb
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$ echo
$ORACLE_DBNAME
ORA12R1
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$ echo
$ORACLE_DBNAME_LOWER
ora12r1
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$ echo
$ORACLE_SID
ORA12R1
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$ echo
$ORACLE_SID_LOWER
ora12r1
3-5. Standby DB에서 pfile 설정
[ORA12R1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$ cp /home/oracle/BACKUP_FOR_ADG/initORA12R1.ora /home/oracle/BACKUP_FOR_ADG/initORA12R1.ora.bak
[ORA12R1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$ vi initORA12R1.ora
--아래와 같이 pfile 을 수정함
[ORA12R1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$
cat initORA12R1.ora
*.audit_file_dest='/u01/app/oracle/admin/ORA12R1_STB/adump'
*.audit_sys_operations=TRUE
*.audit_trail='OS'
*.cluster_database=false
*.compatible='12.2.0'
*.control_files='+FRA1/ORA12R1_STB/CONTROLFILE/current.289.1211838791','+DATA1/ORA12R1_STB/CONTROLFILE/current.279.1211838791'#Restore
Controlfile
*.db_block_size=8192
*.db_create_file_dest='+DATA1'
*.db_create_online_log_dest_1='+FRA1'
*.db_create_online_log_dest_2='+DATA1'
*.db_file_name_convert='ORA12R1','ORA12R1_STB'
*.db_name='ORA12R1'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA12R1XDB)'
*.fal_client='TNS_ORA12R1_STB'
*.fal_server='TNS_ORA12R1'
family:dw_helper.instance_mode='read-only'
*.local_listener='-oraagent-dummy-'
*.log_archive_config='DG_CONFIG=(ORA12R1,ORA12R1_STB)'
*.log_archive_dest_1='LOCATION=+FRA1
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA12R1_STB'
*.log_archive_dest_2='SERVICE=TNS_ORA12R1 LGWR ASYNC
NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA12R1
REOPEN=5'
*.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_file_name_convert='ORA12R1','ORA12R1_STB'
*.nls_language='AMERICAN'
*.nls_territory='KOREA'
*.open_cursors=300
*.optimizer_adaptive_plans=FALSE
*.optimizer_adaptive_reporting_only=TRUE
*.optimizer_dynamic_sampling=0
*.parallel_min_servers=0
*.pga_aggregate_target=512m
*.processes=1432
*.remote_login_passwordfile='exclusive'
*.resource_manager_plan='FORCE:'
*.sga_target=2048m
*.standby_file_management='AUTO'
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'
*.uniform_log_timestamp_format=FALSE
*.use_large_pages=true
*.instance_name='ORA12R1'
*.db_unique_name='ORA12R1_STB'
4. Standby DB 복구
4-1. Standby DB에서 인스턴스를 nomount 모드로 기동
[ORA12R1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$
alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA12R1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$
ss
SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 18 23:03:57 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
[]<SYS@ORA12R1>$ startup nomount
pfile='/home/oracle/BACKUP_FOR_ADG/initORA12R1.ora'
ORACLE instance started.
Total System
Global Area 2147483648 bytes
Fixed
Size
8622776 bytes
Variable
Size
1040190792 bytes
Database
Buffers 1090519040 bytes
Redo
Buffers
8151040 bytes
4-2. 스탠바이 컨트롤 파일 resotre
--새로운 ssh창을
열어서
접속할
데이터베이스(DB_NAME)를 선택하세요:
1) ORA12S1
2) ORA12R1
3) RESERVED
번호를 입력하세요 (1,2,3): 2
현재 ORACLE_SID: ORA12R1
ORA12R1 환경 설정 적용 중...
환경 설정 완료 : 현재
ORACLE_SID = ORA12R1
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$
--> ORA12R1 선택
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$ ls -laR
/home/oracle/BACKUP_FOR_ADG/
/home/oracle/BACKUP_FOR_ADG/:
total
852440
drwxr-xr-x. 3 oracle
oinstall 4096 Sep 18 23:05 .
drwx------. 10 oracle
oinstall 4096 Sep 20 09:05 ..
-rw-r-----. 1 oracle oinstall 94590464 Sep 18
21:53 arch_ORA12R1_20250918_5h440qns_1_1.bkp
-rw-r-----. 1 oracle oinstall 87975936 Sep 18
21:53 arch_ORA12R1_20250918_5i440qns_1_1.bkp
-rw-r-----. 1 oracle oinstall 7827456 Sep
18 21:53 arch_ORA12R1_20250918_5j440qns_1_1.bkp
-rw-r-----. 1 oracle oinstall 2730496 Sep
18 21:53 arch_ORA12R1_20250918_5k440qns_1_1.bkp
-rw-r-----. 1 oracle oinstall
48128 Sep 18 21:53 arch_ORA12R1_20250918_5l440qnt_1_1.bkp
drwxr-xr-x. 2 oracle
oinstall 99 Sep 18 21:53 autobackup
-rw-r--r--. 1 oracle
oinstall 1736 Sep 18 23:05 initORA12R1.ora
-rw-r--r--. 1 oracle
oinstall 1737 Sep 18 23:03 initORA12R1.ora.bak
-rw-r-----. 1 oracle oinstall 255705088 Sep 18 21:53
ORA12R1_L0_20250918_5c440qms_1_1.bkp
-rw-r-----. 1
oracle oinstall 385744896 Sep 18 21:53 ORA12R1_L0_20250918_5d440qms_1_1.bkp
-rw-r-----. 1 oracle oinstall 3203072 Sep
18 21:53 ORA12R1_L0_20250918_5e440qms_1_1.bkp
-rw-r-----. 1 oracle oinstall 14843904 Sep 18
21:53 ORA12R1_L0_20250918_5f440qms_1_1.bkp
-rw-r--r--. 1 oracle
oinstall 3584 Sep 18 21:53 orapwORA12R1
-rw-r-----. 1 oracle oinstall 20201472 Sep 18
21:53 STB_CTL_5n440qo2_1_1_20250918
/home/oracle/BACKUP_FOR_ADG/autobackup:
total 59284
drwxr-xr-x. 2 oracle
oinstall 99 Sep 18 21:53 .
drwxr-xr-x. 3 oracle oinstall 4096
Sep 18 23:05 ..
-rw-r-----. 1 oracle oinstall 20234240
Sep 18 21:53 c-270370777-20250918-05
-rw-r-----. 1
oracle oinstall 20234240 Sep 18 21:53 c-270370777-20250918-06
-rw-r-----. 1 oracle oinstall 20234240 Sep 18 21:53
c-270370777-20250918-07
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$ alias
rt
alias rt='rman
target /'
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$
rt
Recovery Manager: Release 12.2.0.1.0 - Production on Sat Sep 20 09:06:48 2025
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA12R1 (not mounted)
RMAN> restore controlfile from '/home/oracle/BACKUP_FOR_ADG/STB_CTL_5n440qo2_1_1_20250918';
Starting restore
at 25/09/20
using channel ORA_DISK_1
channel
ORA_DISK_1: restoring control file
channel ORA_DISK_1:
restore complete, elapsed time: 00:00:01
output file
name=+FRA1/ORA12R1_STB/CONTROLFILE/current.278.1212311243
output file
name=+DATA1/ORA12R1_STB/CONTROLFILE/current.272.1212311243
Finished restore at 25/09/20
Statement
processed
released channel:
ORA_DISK_1
--grid os
user
[+ASM:grid@ol7ora12s1][/home/grid]$ asmcmd ls -sl
+FRA1/ORA12R1_STB/CONTROLFILE/
Type
Redund Striped
Time
Sys Block_Size Blocks
Bytes Space Name
CONTROLFILE UNPROT FINE
SEP 20 09:00:00 Y
16384 1229 20135936 33554432
current.278.1212311243
[+ASM:grid@ol7ora12s1][/home/grid]$ asmcmd ls -sl
+DATA1/ORA12R1_STB/CONTROLFILE/
Type
Redund Striped
Time
Sys Block_Size Blocks
Bytes Space Name
CONTROLFILE UNPROT FINE
SEP 20 09:00:00 Y
16384 1229 20135936 33554432
current.272.1212311243
RMAN> alter database mount;
[ORA12R1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$
cat /home/oracle/BACKUP_FOR_ADG/initORA12R1.ora | grep -i
controlfile
*.control_files='+FRA1/ORA12R1_STB/CONTROLFILE/current.278.1212311243','+DATA1/ORA12R1_STB/CONTROLFILE/current.272.1212311243'#Restore
Controlfile
--> pfile 수정
4-3. 스탠바이 DB resotre
-- Standby에서
Primary의 백업본을 가져올 때 동일한 디렉토리 경로에 가져왔기 때문에
-- 가져온 백업본을
인식함, 이럴 경우
-- catalog start with
'/home/oracle/BACKUP_FOR_ADG/'
-- 를 할 필요 없음
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV
Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
---------------
157 Incr 0
3.05M
DISK 00:00:00
25/09/18
BP
Key: 157 Status: AVAILABLE Compressed: YES Tag:
TAG20250918T210012
Piece Name:
/home/oracle/BACKUP_FOR_ADG/ORA12R1_L0_20250918_5e440qms_1_1.bkp
List of Datafiles in backup set 157
File LV Type Ckp SCN Ckp Time Abs
Fuz SCN Sparse Name
---- -- ---- ----------
-------- ----------- ------ ----
4 0 Incr 4314784
25/09/18
NO +DATA1/MUST_RENAME_THIS_DATAFILE_4.4294967295.4294967295
5 0 Incr
4314784
25/09/18
NO
+DATA1/MUST_RENAME_THIS_DATAFILE_5.4294967295.4294967295
BS Key Type
LV Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
---------------
158 Incr 0
14.15M DISK
00:00:01 25/09/18
BP Key:
158 Status: AVAILABLE Compressed: YES Tag:
TAG20250918T210012
Piece Name:
/home/oracle/BACKUP_FOR_ADG/ORA12R1_L0_20250918_5f440qms_1_1.bkp
List of Datafiles in backup set 158
File LV Type Ckp SCN Ckp Time Abs
Fuz SCN Sparse Name
---- -- ---- ----------
-------- ----------- ------ ----
7 0 Incr 4314787
25/09/18
NO +DATA1/MUST_RENAME_THIS_DATAFILE_7.4294967295.4294967295
8 0 Incr
4314787
25/09/18
NO
+DATA1/MUST_RENAME_THIS_DATAFILE_8.4294967295.4294967295
BS Key Type
LV Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
---------------
159 Incr 0
243.85M DISK
00:00:12 25/09/18
BP Key:
159 Status: AVAILABLE Compressed: YES Tag:
TAG20250918T210012
Piece Name:
/home/oracle/BACKUP_FOR_ADG/ORA12R1_L0_20250918_5c440qms_1_1.bkp
List of Datafiles in backup set 159
File LV Type Ckp SCN Ckp Time Abs
Fuz SCN Sparse Name
---- -- ---- ----------
-------- ----------- ------ ----
1 0 Incr 4314782
25/09/18
NO +DATA1/MUST_RENAME_THIS_DATAFILE_1.4294967295.4294967295
2 0 Incr
4314782
25/09/18
NO
+DATA1/MUST_RENAME_THIS_DATAFILE_2.4294967295.4294967295
BS Key Type
LV Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
---------------
160 Incr 0
367.87M DISK
00:00:20 25/09/18
BP Key:
160 Status: AVAILABLE Compressed: YES Tag:
TAG20250918T210012
Piece Name:
/home/oracle/BACKUP_FOR_ADG/ORA12R1_L0_20250918_5d440qms_1_1.bkp
List of Datafiles in backup set 160
File LV Type Ckp SCN Ckp Time Abs
Fuz SCN Sparse Name
---- -- ---- ----------
-------- ----------- ------ ----
3 0 Incr 4314783
25/09/18
NO
+DATA1/MUST_RENAME_THIS_DATAFILE_3.4294967295.4294967295
BS Key Type
LV Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
---------------
161
Full 19.28M
DISK 00:00:00
25/09/18
BP
Key: 161 Status: AVAILABLE Compressed: NO Tag:
TAG20250918T210037
Piece Name:
/home/oracle/BACKUP_FOR_ADG/autobackup/c-270370777-20250918-05
SPFILE Included: Modification time: 25/09/18
SPFILE db_unique_name: ORA12R1
Control File Included: Ckp SCN:
4314846 Ckp time: 25/09/18
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- ----------- ------------
---------------
162
2.60M
DISK 00:00:00
25/09/18
BP
Key: 162 Status: AVAILABLE Compressed: YES Tag:
TAG20250918T210044
Piece Name:
/home/oracle/BACKUP_FOR_ADG/arch_ORA12R1_20250918_5k440qns_1_1.bkp
List of
Archived Logs in backup set 162
Thrd
Seq Low SCN Low Time Next
SCN Next Time
---- ------- ----------
-------- ---------- ---------
1
13 4312141 25/09/18
4312165 25/09/18
1 14 4312165
25/09/18 4314726 25/09/18
2 12 4311964
25/09/18 4312148 25/09/18
2 13 4312148
25/09/18 4312176 25/09/18
2 14 4312176
25/09/18 4314733 25/09/18
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- ----------- ------------
---------------
163
7.46M
DISK 00:00:00
25/09/18
BP
Key: 163 Status: AVAILABLE Compressed: YES Tag:
TAG20250918T210044
Piece Name:
/home/oracle/BACKUP_FOR_ADG/arch_ORA12R1_20250918_5j440qns_1_1.bkp
List of
Archived Logs in backup set 163
Thrd
Seq Low SCN Low Time Next
SCN Next Time
---- ------- ----------
-------- ---------- ---------
1
10 4301823 25/09/18
4302698 25/09/18
1 11 4302698
25/09/18 4311957 25/09/18
1 12 4311957
25/09/18 4312141 25/09/18
2 10 4301820
25/09/18 4302707 25/09/18
2 11 4302707
25/09/18 4311964 25/09/18
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- ----------- ------------
---------------
164
46.50K DISK
00:00:00 25/09/18
BP Key:
164 Status: AVAILABLE Compressed: YES Tag:
TAG20250918T210044
Piece Name:
/home/oracle/BACKUP_FOR_ADG/arch_ORA12R1_20250918_5l440qnt_1_1.bkp
List of
Archived Logs in backup set 164
Thrd
Seq Low SCN Low Time Next
SCN Next Time
---- ------- ----------
-------- ---------- ---------
1
15 4314726 25/09/18
4314883 25/09/18
1 16 4314883
25/09/18 4314907 25/09/18
2 15 4314733
25/09/18 4314890 25/09/18
2 16 4314890
25/09/18 4314912 25/09/18
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- ----------- ------------
---------------
165
83.90M DISK
00:00:04 25/09/18
BP Key:
165 Status: AVAILABLE Compressed: YES Tag:
TAG20250918T210044
Piece Name:
/home/oracle/BACKUP_FOR_ADG/arch_ORA12R1_20250918_5i440qns_1_1.bkp
List of
Archived Logs in backup set 165
Thrd
Seq Low SCN Low Time Next
SCN Next Time
---- ------- ----------
-------- ---------- ---------
1
9 4225341 25/09/18
4301823 25/09/18
2 9
4226976 25/09/18 4301820
25/09/18
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- ----------- ------------
---------------
166
90.21M DISK
00:00:04 25/09/18
BP Key:
166 Status: AVAILABLE Compressed: YES Tag:
TAG20250918T210044
Piece Name:
/home/oracle/BACKUP_FOR_ADG/arch_ORA12R1_20250918_5h440qns_1_1.bkp
List of
Archived Logs in backup set 166
Thrd
Seq Low SCN Low Time Next
SCN Next Time
---- ------- ----------
-------- ---------- ---------
1
8 4142732 25/09/18
4225341 25/09/18
2 8
4142729 25/09/18 4226976
25/09/18
BS Key Type
LV Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
---------------
167
Full 19.28M
DISK 00:00:00
25/09/18
BP
Key: 167 Status: AVAILABLE Compressed: NO Tag:
TAG20250918T210048
Piece Name:
/home/oracle/BACKUP_FOR_ADG/autobackup/c-270370777-20250918-06
SPFILE Included: Modification time: 25/09/18
SPFILE db_unique_name: ORA12R1
Control File Included: Ckp SCN:
4314972 Ckp time: 25/09/18
[]<SYS@ORA12R1>$
col set_new_name for a50
col name
for a80
select 'set newname for datafile ' || file# || '
to ' || ''''|| '+DATA1' || '''' || ';' as set_new_name, name from
v$datafile;
SET_NEW_NAME
NAME
--------------------------------------------------
--------------------------------------------------------------------------------
set newname for datafile 1 to
'+DATA1';
+DATA1/MUST_RENAME_THIS_DATAFILE_1.4294967295.4294967295
set newname for datafile 2 to
'+DATA1';
+DATA1/MUST_RENAME_THIS_DATAFILE_2.4294967295.4294967295
set newname for datafile 3 to
'+DATA1';
+DATA1/MUST_RENAME_THIS_DATAFILE_3.4294967295.4294967295
set newname for datafile 4 to
'+DATA1';
+DATA1/MUST_RENAME_THIS_DATAFILE_4.4294967295.4294967295
set newname for datafile 5 to
'+DATA1';
+DATA1/MUST_RENAME_THIS_DATAFILE_5.4294967295.4294967295
set newname for datafile 7 to
'+DATA1';
+DATA1/MUST_RENAME_THIS_DATAFILE_7.4294967295.4294967295
set newname for datafile 8 to
'+DATA1';
+DATA1/MUST_RENAME_THIS_DATAFILE_8.4294967295.4294967295
--> SET_NEW_NAME 의 결과를 사용해도 되고
-- 아래와 같이 for database를
사용해도됨
SET NEWNAME FOR DATABASE TO '+DATA1';
run
{
SET NEWNAME FOR DATABASE TO
'+DATA1';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
}
executing command: SET NEWNAME
Starting restore
at 25/09/20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1637 device
type=DISK
channel
ORA_DISK_1: starting datafile backup set restore
channel
ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to +DATA1
channel ORA_DISK_1: restoring datafile 00005 to +DATA1
channel ORA_DISK_1: reading from backup piece
/home/oracle/BACKUP_FOR_ADG/ORA12R1_L0_20250918_5e440qms_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/BACKUP_FOR_ADG/ORA12R1_L0_20250918_5e440qms_1_1.bkp
tag=TAG20250918T210012
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:01
channel ORA_DISK_1: starting
datafile backup set restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to +DATA1
channel ORA_DISK_1: restoring datafile 00008 to +DATA1
channel ORA_DISK_1: reading from backup piece
/home/oracle/BACKUP_FOR_ADG/ORA12R1_L0_20250918_5f440qms_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/BACKUP_FOR_ADG/ORA12R1_L0_20250918_5f440qms_1_1.bkp
tag=TAG20250918T210012
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:03
channel ORA_DISK_1: starting
datafile backup set restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA1
channel ORA_DISK_1: restoring datafile 00002 to +DATA1
channel ORA_DISK_1: reading from backup piece
/home/oracle/BACKUP_FOR_ADG/ORA12R1_L0_20250918_5c440qms_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/BACKUP_FOR_ADG/ORA12R1_L0_20250918_5c440qms_1_1.bkp
tag=TAG20250918T210012
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:25
channel ORA_DISK_1: starting
datafile backup set restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to +DATA1
channel ORA_DISK_1: reading from backup piece
/home/oracle/BACKUP_FOR_ADG/ORA12R1_L0_20250918_5d440qms_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/BACKUP_FOR_ADG/ORA12R1_L0_20250918_5d440qms_1_1.bkp
tag=TAG20250918T210012
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:25
Finished restore at
25/09/20
datafile 1
switched to datafile copy
input datafile copy RECID=8
STAMP=1212312102 file name=+DATA1/ORA12R1_STB/DATAFILE/system.277.1212312053
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=1212312102 file
name=+DATA1/ORA12R1_STB/DATAFILE/tuner_data1.276.1212312053
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=1212312102 file
name=+DATA1/ORA12R1_STB/DATAFILE/sysaux.278.1212312077
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=1212312102 file
name=+DATA1/ORA12R1_STB/DATAFILE/undotbs1.271.1212312049
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=1212312103 file
name=+DATA1/ORA12R1_STB/DATAFILE/undotbs2.273.1212312049
datafile 7 switched to datafile copy
input datafile copy RECID=13 STAMP=1212312103 file
name=+DATA1/ORA12R1_STB/DATAFILE/users.275.1212312049
datafile 8 switched to datafile copy
input datafile copy RECID=14 STAMP=1212312103 file
name=+DATA1/ORA12R1_STB/DATAFILE/tuner_idx1.274.1212312049
renamed tempfile 1
to +DATA1 in control file
renamed tempfile 2 to +DATA1
in control file
[+ASM:grid@ol7ora12s1][/home/grid]$ asmcmd ls -sl
+DATA1/ORA12R1_STB/DATAFILE/
Type Redund
Striped
Time
Sys Block_Size Blocks
Bytes Space Name
DATAFILE UNPROT COARSE SEP 20
09:00:00 Y
8192 250113 2048925696 2055208960
SYSAUX.278.1212312077
DATAFILE UNPROT
COARSE SEP 20 09:00:00
Y 8192
106241 870326272 876609536
SYSTEM.277.1212312053
DATAFILE UNPROT
COARSE SEP 20 09:00:00
Y 8192 196609
1610620928 1619001344 TUNER_DATA1.276.1212312053
DATAFILE UNPROT COARSE SEP 20
09:00:00 Y
8192 131073 1073750016 1082130432
TUNER_IDX1.274.1212312049
DATAFILE UNPROT
COARSE SEP 20 09:00:00
Y 8192 122241
1001398272 1006632960 UNDOTBS1.271.1212312049
DATAFILE UNPROT COARSE SEP 20
09:00:00 Y
8192 12801 104865792 109051904
UNDOTBS2.273.1212312049
DATAFILE UNPROT
COARSE SEP 20 09:00:00
Y
8192 641
5251072 8388608
USERS.275.1212312049
[]<SYS@ORA12R1>$ select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA1/ORA12R1_STB/DATAFILE/system.277.1212312053
+DATA1/ORA12R1_STB/DATAFILE/tuner_data1.276.1212312053
+DATA1/ORA12R1_STB/DATAFILE/sysaux.278.1212312077
+DATA1/ORA12R1_STB/DATAFILE/undotbs1.271.1212312049
+DATA1/ORA12R1_STB/DATAFILE/undotbs2.273.1212312049
+DATA1/ORA12R1_STB/DATAFILE/users.275.1212312049
+DATA1/ORA12R1_STB/DATAFILE/tuner_idx1.274.1212312049
7 rows selected.
Elapsed: 00:00:00.00
[]<SYS@ORA12R1>$ select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA1
+DATA1
2 rows selected.
Elapsed: 00:00:00.01
RMAN> report schema;
RMAN-06139:
warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name
ORA12R1_STB
List of Permanent
Datafiles
===========================
File Size(MB)
Tablespace RB segs
Datafile Name
---- -------- -------------------- -------
------------------------
1
830
SYSTEM
*** +DATA1/ORA12R1_STB/DATAFILE/system.277.1212312053
2 1536
TUNER_DATA1
***
+DATA1/ORA12R1_STB/DATAFILE/tuner_data1.276.1212312053
3 1954
SYSAUX
*** +DATA1/ORA12R1_STB/DATAFILE/sysaux.278.1212312077
4 955
UNDOTBS1
***
+DATA1/ORA12R1_STB/DATAFILE/undotbs1.271.1212312049
5 100
UNDOTBS2
***
+DATA1/ORA12R1_STB/DATAFILE/undotbs2.273.1212312049
7
5
USERS
*** +DATA1/ORA12R1_STB/DATAFILE/users.275.1212312049
8 1024
TUNER_IDX1
***
+DATA1/ORA12R1_STB/DATAFILE/tuner_idx1.274.1212312049
List of Temporary
Files
=======================
File Size(MB)
Tablespace
Maxsize(MB) Tempfile Name
---- --------
-------------------- ----------- --------------------
1 20
TEMP
32767 +DATA1
2 128
TUNER_TEMP
32767 +DATA1
4-4. 스탠바이 DB recover
RMAN> recover database;
Starting recover
at 25/09/20
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to
default destination
channel ORA_DISK_1: restoring
archived log
archived log thread=1 sequence=15
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=15
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=16
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=16
channel ORA_DISK_1: reading from backup piece
/home/oracle/BACKUP_FOR_ADG/arch_ORA12R1_20250918_5l440qnt_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/BACKUP_FOR_ADG/arch_ORA12R1_20250918_5l440qnt_1_1.bkp
tag=TAG20250918T210044
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:01
archived log file
name=+FRA1/ORA12R1_STB/ARCHIVELOG/2025_09_20/thread_1_seq_15.279.1212312611
thread=1 sequence=15
archived log file
name=+FRA1/ORA12R1_STB/ARCHIVELOG/2025_09_20/thread_2_seq_15.277.1212312611
thread=2 sequence=15
archived log file
name=+FRA1/ORA12R1_STB/ARCHIVELOG/2025_09_20/thread_1_seq_16.276.1212312611
thread=1 sequence=16
archived log file
name=+FRA1/ORA12R1_STB/ARCHIVELOG/2025_09_20/thread_2_seq_16.275.1212312611
thread=2 sequence=16
media recovery complete, elapsed
time: 00:00:01
Finished recover at 25/09/20
-->
thread1의 16, thread2의 16까지 복구된 것을 알 수 있음
--Primary DB에서 아래 상황
확인
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ alias
cdos
alias cdos='cd
/home/oracle/oracle_scripts'
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$
cdos
[ORA12R11:oracle@ol7ora12r11][/home/oracle/oracle_scripts]$
ls
1.USER_TABLESPACE_SYS_SYSTEM_v1_0.sql
2.TABLE_INDEX_MERGE_STATISTICS_TUNER_v1_1.sql adg_par.sql
rdl.sql stb_rdl.sql
[ORA12R11:oracle@ol7ora12r11][/home/oracle/oracle_scripts]$
ss
SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 20 09:34:25 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ol7ora12r11]<SYS@ORA12R11>$ @rdl
GROUP#
THREAD# SEQUENCE#
MEMBER
STATUS
TYPE
MB STATUS
------ ------- ----------
-------------------------------------------------- --------------- ----------
------------ ---------------
1
1 22
+DATA1/ORA12R1/ONLINELOG/group_1.274.1211843917
(NULL)
ONLINE
200 INACTIVE
1
1 22
+FRA1/ORA12R1/ONLINELOG/group_1.299.1211843917
(NULL)
ONLINE
200 INACTIVE
2
1 23
+FRA1/ORA12R1/ONLINELOG/group_2.258.1211843917
(NULL)
ONLINE
200 CURRENT
2
1 23
+DATA1/ORA12R1/ONLINELOG/group_2.278.1211843917
(NULL)
ONLINE
200 CURRENT
3
1 21
+DATA1/ORA12R1/ONLINELOG/group_3.266.1211843917
(NULL)
ONLINE
200 INACTIVE
3
1 21
+FRA1/ORA12R1/ONLINELOG/group_3.257.1211843917
(NULL)
ONLINE
200 INACTIVE
4
2 22
+FRA1/ORA12R1/ONLINELOG/group_4.259.1211843917
(NULL)
ONLINE
200 INACTIVE
4
2 22
+DATA1/ORA12R1/ONLINELOG/group_4.267.1211843917
(NULL)
ONLINE
200 INACTIVE
5
2 23
+FRA1/ORA12R1/ONLINELOG/group_5.256.1211843917
(NULL)
ONLINE
200 CURRENT
5
2 23
+DATA1/ORA12R1/ONLINELOG/group_5.277.1211843917
(NULL)
ONLINE
200 CURRENT
6
2 21
+FRA1/ORA12R1/ONLINELOG/group_6.296.1211843917
(NULL)
ONLINE
200 INACTIVE
6
2 21
+DATA1/ORA12R1/ONLINELOG/group_6.272.1211843917
(NULL)
ONLINE
200 INACTIVE
--> Primary DB는 thread 1의
23, thread 2의 23까지 간 상황
12 rows selected.
Elapsed: 00:00:00.03
[ol7ora12r11]<SYS@ORA12R11>$ @stb_rdl
DUMMY GROUP#
THREAD# SEQUENCE# ARCHIVED STATUS
LOG_STATUS
TYPE MEMBER
-----
------ ------- --------- -------- ------------ --------------------- ----------
------------------------------------------------------------
1
7
1 0
YES UNASSIGNED
(NULL)
STANDBY +DATA1/ORA12R1/ONLINELOG/group_7.273.1212180303
1
7
1 0
YES UNASSIGNED
(NULL)
STANDBY +FRA1/ORA12R1/ONLINELOG/group_7.262.1212180303
1
8
1 0
YES UNASSIGNED
(NULL)
STANDBY +DATA1/ORA12R1/ONLINELOG/group_8.261.1212180305
1
8
1 0
YES UNASSIGNED
(NULL)
STANDBY +FRA1/ORA12R1/ONLINELOG/group_8.301.1212180305
1
9
1 0
YES UNASSIGNED
(NULL)
STANDBY +DATA1/ORA12R1/ONLINELOG/group_9.259.1212180305
1
9
1 0
YES UNASSIGNED
(NULL)
STANDBY +FRA1/ORA12R1/ONLINELOG/group_9.303.1212180305
1
10
1 0
YES UNASSIGNED
(NULL)
STANDBY +DATA1/ORA12R1/ONLINELOG/group_10.258.1212180305
1
10
1 0
YES UNASSIGNED
(NULL)
STANDBY +FRA1/ORA12R1/ONLINELOG/group_10.287.1212180305
1
11
2 0
YES UNASSIGNED
(NULL)
STANDBY +DATA1/ORA12R1/ONLINELOG/group_11.257.1212180305
1
11
2 0
YES UNASSIGNED
(NULL)
STANDBY +FRA1/ORA12R1/ONLINELOG/group_11.300.1212180305
1
12
2 0
YES UNASSIGNED
(NULL)
STANDBY +DATA1/ORA12R1/ONLINELOG/group_12.281.1212180305
1
12
2 0
YES UNASSIGNED
(NULL)
STANDBY +FRA1/ORA12R1/ONLINELOG/group_12.292.1212180305
1
13
2 0
YES UNASSIGNED
(NULL)
STANDBY +DATA1/ORA12R1/ONLINELOG/group_13.282.1212180305
1
13
2 0
YES UNASSIGNED
(NULL)
STANDBY +FRA1/ORA12R1/ONLINELOG/group_13.263.1212180307
1
14
2 0
YES UNASSIGNED
(NULL)
STANDBY +DATA1/ORA12R1/ONLINELOG/group_14.283.1212180309
1
14
2 0
YES UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1/ONLINELOG/group_14.294.1212180309
16 rows selected.
Elapsed: 00:00:00.02
4-5. spfile 생성
RMAN> host 'ls -al /home/oracle/BACKUP_FOR_ADG';
total 852440
drwxr-xr-x. 3 oracle
oinstall 4096 Sep 18 23:05 .
drwx------. 10 oracle
oinstall 4096 Sep 20 09:10 ..
-rw-r-----. 1 oracle oinstall 94590464 Sep 18
21:53 arch_ORA12R1_20250918_5h440qns_1_1.bkp
-rw-r-----. 1 oracle oinstall 87975936 Sep 18
21:53 arch_ORA12R1_20250918_5i440qns_1_1.bkp
-rw-r-----. 1 oracle oinstall 7827456 Sep
18 21:53 arch_ORA12R1_20250918_5j440qns_1_1.bkp
-rw-r-----. 1 oracle oinstall 2730496 Sep
18 21:53 arch_ORA12R1_20250918_5k440qns_1_1.bkp
-rw-r-----. 1 oracle oinstall
48128 Sep 18 21:53 arch_ORA12R1_20250918_5l440qnt_1_1.bkp
drwxr-xr-x. 2 oracle
oinstall 99 Sep 18 21:53 autobackup
-rw-r--r--. 1 oracle
oinstall 1736 Sep 18 23:05 initORA12R1.ora
-rw-r--r--. 1 oracle
oinstall 1737 Sep 18 23:03 initORA12R1.ora.bak
-rw-r-----. 1 oracle oinstall 255705088 Sep 18 21:53
ORA12R1_L0_20250918_5c440qms_1_1.bkp
-rw-r-----. 1
oracle oinstall 385744896 Sep 18 21:53 ORA12R1_L0_20250918_5d440qms_1_1.bkp
-rw-r-----. 1 oracle oinstall 3203072 Sep
18 21:53 ORA12R1_L0_20250918_5e440qms_1_1.bkp
-rw-r-----. 1 oracle oinstall 14843904 Sep 18
21:53 ORA12R1_L0_20250918_5f440qms_1_1.bkp
-rw-r--r--. 1 oracle
oinstall 3584 Sep 18 21:53 orapwORA12R1
-rw-r-----. 1 oracle oinstall 20201472 Sep 18
21:53 STB_CTL_5n440qo2_1_1_20250918
host command
complete
RMAN> create spfile='+DATA1' from pfile='/home/oracle/BACKUP_FOR_ADG/initORA12R1.ora';
Statement processed
[+ASM:grid@ol7ora12s1][/home/grid]$ asmcmd ls -sl
+DATA1/ORA12R1_STB/PARAMETERFILE
Type
Redund Striped
Time
Sys Block_Size Blocks Bytes Space
Name
PARAMETERFILE UNPROT COARSE
SEP 20 10:00:00
Y
512 9 4608 4194304
spfile.281.1212316557
4-6. pwfile을 ASM영역으로 복사
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$ cdbfa
[ORA12R1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$
ls -l
total 852432
-rw-r-----. 1 oracle
oinstall 94590464 Sep 18 21:53 arch_ORA12R1_20250918_5h440qns_1_1.bkp
-rw-r-----. 1 oracle oinstall 87975936 Sep 18 21:53
arch_ORA12R1_20250918_5i440qns_1_1.bkp
-rw-r-----. 1
oracle oinstall 7827456 Sep 18 21:53
arch_ORA12R1_20250918_5j440qns_1_1.bkp
-rw-r-----. 1
oracle oinstall 2730496 Sep 18 21:53
arch_ORA12R1_20250918_5k440qns_1_1.bkp
-rw-r-----. 1
oracle oinstall 48128 Sep 18 21:53
arch_ORA12R1_20250918_5l440qnt_1_1.bkp
drwxr-xr-x. 2
oracle oinstall 99 Sep 18 21:53
autobackup
-rw-r--r--. 1 oracle
oinstall 1736 Sep 18 23:05 initORA12R1.ora
-rw-r--r--. 1 oracle oinstall
1737 Sep 18 23:03 initORA12R1.ora.bak
-rw-r-----. 1
oracle oinstall 255705088 Sep 18 21:53 ORA12R1_L0_20250918_5c440qms_1_1.bkp
-rw-r-----. 1 oracle oinstall 385744896 Sep 18 21:53
ORA12R1_L0_20250918_5d440qms_1_1.bkp
-rw-r-----. 1
oracle oinstall 3203072 Sep 18 21:53
ORA12R1_L0_20250918_5e440qms_1_1.bkp
-rw-r-----. 1
oracle oinstall 14843904 Sep 18 21:53
ORA12R1_L0_20250918_5f440qms_1_1.bkp
-rw-r--r--. 1
oracle oinstall 3584 Sep 18 21:53 orapwORA12R1
-rw-r-----. 1 oracle oinstall 20201472 Sep 18 21:53
STB_CTL_5n440qo2_1_1_20250918
[ORA12R1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$
cp orapwORA12R1 /tmp
[ORA12R1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$
chmod 664 /tmp/orapwORA12R1
[+ASM:grid@ol7ora12s1][/home/grid]$ ls -l
/tmp/orapwORA12R1
-rw-rw-r--. 1 oracle oinstall 3584 Sep 20 10:07
/tmp/orapwORA12R1
[+ASM:grid@ol7ora12s1][/home/grid]$ asmcmd pwcopy
--dbuniquename ORA12R1_STB -f /tmp/orapwORA12R1
+DATA1/ORA12R1_STB/PASSWORD/orapwORA12R1
PRCD-1120 : The resource for database ORA12R1_STB could not
be found.
PRCR-1001 : Resource ora.ora12r1_stb.db does
not exist
ASMCMD-9457: an internally issued srvctl
command failed with errors
copying /tmp/orapwORA12R1
-> +DATA1/ORA12R1_STB/PASSWORD/orapwORA12R1
ASMCMD-9453: failed to register password file as a CRS
resource
[+ASM:grid@ol7ora12s1][/home/grid]$ asmcmd ls -sl
+DATA1/ORA12R1_STB/PASSWORD/orapwORA12R1
Type Redund
Striped
Time
Sys Block_Size Blocks Bytes Space Name
PASSWORD UNPROT COARSE SEP 20
10:00:00 N
512 7
3584 0 orapwORA12R1 =>
+DATA1/ORA12R1_STB/PASSWORD/pwdora12r1_stb.280.1212315023
4-7. crs에 standby DB 등록
[ORA12R1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$
srvctl add
database \
-db ORA12R1_STB \
-dbname ORA12R1 \
-oraclehome
/u01/app/oracle/product/12c/db_1 \
-pwfile
'+DATA1/ORA12R1_STB/PASSWORD/pwdora12r1_stb.280.1212315023' \
-spfile
'+DATA1/ORA12R1_STB/PARAMETERFILE/spfile.281.1212316557' \
-role PHYSICAL_STANDBY \
-startoption "READ ONLY" \
-instance ORA12R1
[ORA12R1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$
csrt
--------------------------------------------------------------------------------
Name
Target State
Server
State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA1.dg
ONLINE ONLINE
ol7ora12s1
STABLE
ora.FRA1.dg
ONLINE ONLINE
ol7ora12s1
STABLE
ora.LISTENER.lsnr
ONLINE ONLINE
ol7ora12s1
STABLE
ora.asm
ONLINE ONLINE
ol7ora12s1
Started,STABLE
ora.ons
OFFLINE OFFLINE
ol7ora12s1
STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE
ONLINE
ol7ora12s1
STABLE
ora.diskmon
1 OFFLINE
OFFLINE
STABLE
ora.evmd
1 ONLINE
ONLINE
ol7ora12s1
STABLE
ora.ora12r1_stb.db
1 OFFLINE
OFFLINE
STABLE
ora.ora12s1.db
1 ONLINE
ONLINE
ol7ora12s1
Open,HOME=/u01/app/o
racle/product/12c/db
_1,STABLE
--------------------------------------------------------------------------------
--> ora12r1_stb 가 신규로 등록됨
--> 현재 운영중인 ora12s1 db는 내림 (원활한 테스트를
위해)
[ORA12R1:oracle@ol7ora12s1][/home/oracle/BACKUP_FOR_ADG]$
srvctl stop database -db ora12s1
4-8. Standby DB 내리고 재기동 (실시간 동기화 시작)
[]<SYS@ORA12R1>$ shutdown immediate;
ORA-01109:
database not open
Database dismounted.
ORACLE
instance shut down.
[ol7ora12s1]<SYS@ORA12R1>$ startup nomount
[ol7ora12s1]<SYS@ORA12R1>$ alter database mount
[ol7ora12s1]<SYS@ORA12R1>$ ALTER DATABASE RECOVER
MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
--> 동기화
시작
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$ cdos
[ORA12R1:oracle@ol7ora12s1][/home/oracle/oracle_scripts]$
vi stb_rdl.sql
[ORA12R1:oracle@ol7ora12s1][/home/oracle/oracle_scripts]$
cat stb_rdl.sql
COLUMN DUMMY FORMAT 999
COLUMN
GROUP# FORMAT 9999
COLUMN THREAD# FORMAT 9999
COLUMN SEQUENCE# FORMAT 9999999
COLUMN ARCHIVED FORMAT A8
COLUMN
STATUS FORMAT A12
COLUMN MEMBER FORMAT A60
WORD_WRAPPED
SELECT 1 AS DUMMY,
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.STATUS AS
LOG_STATUS,
b.TYPE,
b.MEMBER
FROM V$STANDBY_LOG a, v$logfile b
WHERE a.group# = b.group#
ORDER BY a.GROUP#;
[ORA12R1:oracle@ol7ora12s1][/home/oracle/oracle_scripts]$
ss
SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 20 10:52:28 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ol7ora12s1]<SYS@ORA12R1>$ @stb_rdl
DUMMY GROUP#
THREAD# SEQUENCE# ARCHIVED STATUS
LOG_STATUS
TYPE
MEMBER
----- ------ ------- --------- --------
------------ --------------------- ---------------------
------------------------------------------------------------
1
7
1 27 YES
ACTIVE
(NULL)
STANDBY
+FRA1/ORA12R1_STB/ONLINELOG/group_7.273.1212316663
1
7
1 27 YES
ACTIVE
(NULL)
STANDBY
+DATA1/ORA12R1_STB/ONLINELOG/group_7.283.1212316663
1
8
1 0
NO UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1_STB/ONLINELOG/group_8.268.1212316663
1
8
1 0
NO UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1_STB/ONLINELOG/group_8.284.1212316663
1
9
1 0
NO UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1_STB/ONLINELOG/group_9.267.1212316663
1
9
1 0
NO UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1_STB/ONLINELOG/group_9.285.1212316663
1
10
1 0
NO UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1_STB/ONLINELOG/group_10.274.1212316663
1
10
1 0
NO UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1_STB/ONLINELOG/group_10.282.1212316663
1
11
2 27 YES
ACTIVE
(NULL)
STANDBY
+FRA1/ORA12R1_STB/ONLINELOG/group_11.261.1212316665
1
11
2 27 YES
ACTIVE
(NULL)
STANDBY
+DATA1/ORA12R1_STB/ONLINELOG/group_11.287.1212316665
1
12
2 0
NO UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1_STB/ONLINELOG/group_12.280.1212316665
1
12
2 0
NO UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1_STB/ONLINELOG/group_12.288.1212316665
1
13
2 0
NO UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1_STB/ONLINELOG/group_13.283.1212316667
1
13
2 0
NO UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1_STB/ONLINELOG/group_13.289.1212316667
1
14
2 0
NO UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1_STB/ONLINELOG/group_14.260.1212316665
1
14
2 0
NO UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1_STB/ONLINELOG/group_14.286.1212316665
16 rows selected.
Elapsed: 00:00:00.00
--동기화 중지 후
[ol7ora12s1]<SYS@ORA12R1>$ ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
CANCEL;
--read only로
오픈
[ol7ora12s1]<SYS@ORA12R1>$ alter database open read only;
--다시 동기화 시작
[ol7ora12s1]<SYS@ORA12R1>$ ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING
CURRENT LOGFILE DISCONNECT FROM SESSION;
--> 여기서 만약 open read only까지
못간다면 아래의 절차 수행해봄 - 시작
shutdown immediate;
startup
nomount
alter database mount
ALTER DATABASE
DROP LOGFILE GROUP 7;
ALTER DATABASE DROP LOGFILE GROUP
8;
ALTER DATABASE DROP LOGFILE GROUP 9;
ALTER DATABASE DROP LOGFILE GROUP 10;
ALTER DATABASE DROP LOGFILE GROUP 11;
ALTER DATABASE DROP LOGFILE GROUP 12;
ALTER DATABASE DROP LOGFILE GROUP 13;
ALTER DATABASE DROP LOGFILE GROUP 14;
ALTER DATABASE ADD
STANDBY LOGFILE THREAD 1 group 7('+DATA1','+FRA1') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group
8('+DATA1','+FRA1') SIZE 200M;
ALTER DATABASE ADD
STANDBY LOGFILE THREAD 1 group 9('+DATA1','+FRA1') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group
10('+DATA1','+FRA1') SIZE 200M;
ALTER DATABASE ADD
STANDBY LOGFILE THREAD 2 group 11('+DATA1','+FRA1') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 group
12('+DATA1','+FRA1') SIZE 200M;
ALTER DATABASE ADD
STANDBY LOGFILE THREAD 2 group 13('+DATA1','+FRA1') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 group
14('+DATA1','+FRA1') SIZE 200M;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING
CURRENT LOGFILE DISCONNECT FROM SESSION;
--> 파라미터 문제일 수도 있음, 아카이브를 왜 못받는지 찾아내야함
--> 여기서 만약 open read only까지 못간다면 아래의 절차 수행해봄 - 종료
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$
csrt
--------------------------------------------------------------------------------
Name
Target State
Server
State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA1.dg
ONLINE ONLINE
ol7ora12s1
STABLE
ora.FRA1.dg
ONLINE ONLINE
ol7ora12s1
STABLE
ora.LISTENER.lsnr
ONLINE ONLINE
ol7ora12s1
STABLE
ora.asm
ONLINE ONLINE
ol7ora12s1
Started,STABLE
ora.ons
OFFLINE OFFLINE
ol7ora12s1
STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE
ONLINE
ol7ora12s1
STABLE
ora.diskmon
1 OFFLINE
OFFLINE
STABLE
ora.evmd
1 ONLINE
ONLINE
ol7ora12s1
STABLE
ora.ora12r1_stb.db
1 ONLINE
ONLINE
ol7ora12s1
Open,Readonly,HOME=/
u01/app/oracle/produ
ct/12c/db_1,STABLE
ora.ora12s1.db
1 OFFLINE
OFFLINE
Instance Shutdown,ST
ABLE
--------------------------------------------------------------------------------
4-9. Standby DB 기타 설정
[+ASM:grid@ol7ora12s1][/home/grid]$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 20-SEP-2025 10:57:10
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7ora12s1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias
LISTENER
Version
TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start
Date
17-SEP-2025 16:24:48
Uptime
2 days 18 hr. 32 min. 22 sec
Trace
Level
off
Security
ON: Local OS Authentication
SNMP
OFF
Listener Parameter File
/u01/app/12c/grid/network/admin/listener.ora
Listener
Log File
/u01/app/oracle/diag/tnslsnr/ol7ora12s1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7ora12s1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "+ASM"
has 1 instance(s).
Instance "+ASM", status READY,
has 1 handler(s) for this service...
Service
"+ASM_DATA1" has 1 instance(s).
Instance "+ASM",
status READY, has 1 handler(s) for this service...
Service "+ASM_FRA1" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for
this service...
Service "ORA12R1XDB" has 1
instance(s).
Instance "ORA12R1", status READY,
has 1 handler(s) for this service...
Service
"ORA12R1_STB" has 1 instance(s).
Instance
"ORA12R1", status READY, has 1 handler(s) for this service...
The command completed successfully
[ORA12R1:oracle@ol7ora12s1][/u01/app/oracle/product/12c/db_1/network/admin]$
cat tnsnames.ora
# tnsnames.ora Network Configuration File:
/u01/app/oracle/product/12c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration
tools.
ORA12S1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
ol7ora12s1)(PORT = 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME
= ORA12S1)
)
)
LISTENER_ORA12S1
=
(ADDRESS = (PROTOCOL = TCP)(HOST =
ol7ora12s1)(PORT = 1521))
#Standby
TNS_ORA12R1_STB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
TCP)(HOST = 192.168.240.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =
ORA12R1_STB)
)
)
--> 이걸 Client PC의
tnsnames.ora에 등록하고 오렌지로 접속해봄
#Primary
TNS_ORA12R1 =
(DESCRIPTION
=
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =
TCP)(HOST = 192.168.240.11)(PORT = 1521))
(ADDRESS = (PROTOCOL =
TCP)(HOST = 192.168.240.12)(PORT = 1521))
(LOAD_BALANCE = OFF)
(FAILOVER = ON)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA12R1)
(FAILOVER_MODE = (TYPE =
SELECT)(METHOD = BASIC))
)
)
[ol7ora12s1]<SYS@ORA12R1>$
col name for
a80
select name from
v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA1/ORA12R1_STB/TEMPFILE/temp.296.1212317587
+DATA1/ORA12R1_STB/TEMPFILE/tuner_temp.297.1212317587
2 rows selected.
Elapsed: 00:00:00.00
[ol7ora12s1]<SYS@ORA12R1>$ select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA1/ORA12R1_STB/DATAFILE/system.277.1212312053
+DATA1/ORA12R1_STB/DATAFILE/tuner_data1.276.1212312053
+DATA1/ORA12R1_STB/DATAFILE/sysaux.278.1212312077
+DATA1/ORA12R1_STB/DATAFILE/undotbs1.271.1212312049
+DATA1/ORA12R1_STB/DATAFILE/undotbs2.273.1212312049
+DATA1/ORA12R1_STB/DATAFILE/users.275.1212312049
+DATA1/ORA12R1_STB/DATAFILE/tuner_idx1.274.1212312049
7 rows selected.
Elapsed: 00:00:00.00
4-10. Primary DB에서
아카이브 못지우게 설정
--primary에서
--RMAN 백업 시 아래 설정 추가
CONFIGURE ARCHIVELOG
DELETION POLICY TO APPLIED ON ALL STANDBY;
5. 동기화 테스트
5-1. Primary DB에서
작업
create user
adg_test identified by oracle;
GRANT RESOURCE, CONNECT
TO adg_test;
ALTER USER adg_test ACCOUNT UNLOCK;
ALTER USER adg_test DEFAULT TABLESPACE users;
ALTER USER adg_test TEMPORARY TABLESPACE temp;
ALTER USER adg_test quota unlimited on
users;
create table
adg_test.tb_adg_test_01
(
adg_test_01_no number(15)
)
;
insert into adg_test.tb_adg_test_01 values (1);
commit;
insert into
adg_test.tb_adg_test_01
select (select
nvl(max(adg_test_01_no), 0) from adg_test.tb_adg_test_01)+level from dual
connect by level <= 1000000
;
commit;
5-2. Standby DB에서 확인
SQL> select count(*) from adg_test.TB_ADG_TEST_01;
COUNT(*)
---------
1000001
1 rows selected.
6. 모니터링 방법 정리
6-1. primary DB에서
확인
SELECT *
FROM V$ARCHIVED_LOG A
WHERE 1=1
AND
A.STANDBY_DEST = 'YES'
AND A.APPLIED =
'NO'
ORDER BY A.THREAD#, A.SEQUENCE#
;
SELECT STATUS
, INSTANCE_NAME
, DATABASE_ROLE
, OPEN_MODE
, LOG_MODE
, STARTUP_TIME
, VERSION
, SWITCHOVER_STATUS
FROM V$DATABASE
, GV$INSTANCE
;
6-2. standby DB에서 확인
SELECT *
FROM V$ARCHIVED_LOG A
WHERE 1=1
AND A.APPLIED = 'NO'
ORDER BY A.THREAD#, A.SEQUENCE#
;
SELECT
ARCH.THREAD# "Thread"
,
ARCH.SEQUENCE# "Last Sequence Received"
, APPL.SEQUENCE# "Last Sequence
Applied"
, (ARCH.SEQUENCE# -
APPL.SEQUENCE#) "Difference"
FROM (SELECT THREAD#
,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT
THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH
, (SELECT THREAD# ,SEQUENCE# FROM
V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1
;
SELECT * FROM V$ARCHIVE_DEST;
SELECT PROCESS, STATUS, A.* FROM V$MANAGED_STANDBY A;
SELECT * FROM V$DATAGUARD_STATUS A ORDER BY
MESSAGE_NUM;
SELECT * FROM
V$ARCHIVE_DEST_STATUS;
SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
SELECT STATUS
, INSTANCE_NAME
, DATABASE_ROLE
, OPEN_MODE
, LOG_MODE
, STARTUP_TIME
, VERSION
, SWITCHOVER_STATUS
FROM V$DATABASE
, GV$INSTANCE
;
SELECT THREAD#, SEQUENCE#, APPLIED, STATUS, STAMP, COMPLETION_TIME FROM V$ARCHIVED_LOG;
SELECT STATUS
, GAP_STATUS
FROM GV$ARCHIVE_DEST_STATUS
WHERE DEST_ID = 2;
7. Standby 서버 아카이브 로그 삭제 자동 설정
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$ mkdir -p
/home/oracle/os_scripts/ORA12R1
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$ mkdir -p
/home/oracle/rman_backup/ORA12R1/autobackup
[ORA12R1:oracle@ol7ora12s1][/home/oracle/os_scripts/ORA12R1]$
vi /home/oracle/os_scripts/ORA12R1/rman_init.sh
[ORA12R1:oracle@ol7ora12s1][/home/oracle/os_scripts/ORA12R1]$
cat /home/oracle/os_scripts/ORA12R1/rman_init.sh
#!/bin/bash
export
ORACLE_HOME=/u01/app/oracle/product/12c/db_1
export
ORACLE_SID=ORA12R1
mkdir -p /home/oracle/rman_backup/ORA12R1/autobackup/
$ORACLE_HOME/bin/rman target / << EOF
run {
#CONFIGURE RETENTION POLICY
TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE
DISK TO '/home/oracle/rman_backup/ORA12R1/autobackup/%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'+DATA1/ORA12R1_STB/CONTROLFILE/snapcf_CA.f';
CONFIGURE
DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO
1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE
DISK TO 1;
CONFIGURE ARCHIVELOG DELETION POLICY TO
NONE;
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE
'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE ARCHIVELOG
DELETION POLICY TO APPLIED ON ALL STANDBY; #For ADG
}
exit;
EOF
[ORA12R1:oracle@ol7ora12s1][/home/oracle/os_scripts/ORA12R1]$
vi /home/oracle/os_scripts/ORA12R1/rman_arch.sh
[ORA12R1:oracle@ol7ora12s1][/home/oracle/os_scripts/ORA12R1]$
cat /home/oracle/os_scripts/ORA12R1/rman_arch.sh
export
ORACLE_HOME=/u01/app/oracle/product/12c/db_1
export
ORACLE_SID=ORA12R1
$ORACLE_HOME/bin/rman target / << EOF
run {
crosscheck archivelog all;
delete noprompt force archivelog until time 'SYSDATE-7';
}
exit;
EOF
[ORA12R1:oracle@ol7ora12s1][/home/oracle/os_scripts/ORA12R1]$ chmod 775 rman_arch.sh rman_bakup.sh rman_init.sh
[ORA12R1:oracle@ol7ora12s1][/home/oracle/os_scripts/ORA12R1/rman_log]$ crontab -e
[ORA12R1:oracle@ol7ora12s1][/home/oracle/os_scripts/ORA12R1/rman_log]$
crontab -l | grep rman_backup.sh | grep ORA12R1
20 3 * * * /home/oracle/os_scripts/ORA12R1/rman_backup.sh
&>/dev/null
8. Physical Standby 인스턴스 내렸다 올리기
8-1.
내리기
--Primary 에서
[ol7ora12r11]<SYS@ORA12R11>$ alter system set log_archive_dest_state_2= defer scope=
both sid = '*';
--Standby 에서
[ol7ora12s1]<SYS@ORA12R1>$ alter database recover managed standby database cancel;
[ol7ora12s1]<SYS@ORA12R1>$ shutdown immediate;
8-2. 올리기
--Standby 에서
[ol7ora12s1]<SYS@ORA12R1>$ startup nomount
[ol7ora12s1]<SYS@ORA12R1>$ alter database mount standby database;
[ol7ora12s1]<SYS@ORA12R1>$ alter database recover managed standby database disconnect
from session;
--> 동기화 취소
[ol7ora12s1]<SYS@ORA12R1>$ ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
CANCEL;
--> 실시간 리두로그 까지
동기화
[ol7ora12s1]<SYS@ORA12R1>$ ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING
CURRENT LOGFILE DISCONNECT FROM SESSION;
--Primary 에서
[ol7ora12r11]<SYS@ORA12R11>$ alter system set log_archive_dest_state_2=enable scope =
both sid = '*';
--standby에서 동기화
확인
COL
NAME FOR A40
COL VALUE FOR A20
COL UNIT FOR
A40
COL TIME_COMPUTED FOR A30
COL DATUM_TIME FOR A30
SELECT NAME,
VALUE,
UNIT,
TIME_COMPUTED,
DATUM_TIME
FROM V$DATAGUARD_STATS
ORDER BY NAME;
NAME
VALUE
UNIT
TIME_COMPUTED
DATUM_TIME
----------------------------------------
-------------------- ----------------------------------------
------------------------------ ------------------------------
apply finish
time
(NULL)
day(2) to second(3)
interval
09/20/2025
11:43:19
(NULL)
apply
lag
+00 00:00:00 day(2) to second(0)
interval
09/20/2025
11:43:19
09/20/2025 11:43:18
estimated startup
time
14
second
09/20/2025
11:43:19
(NULL)
transport
lag
+00 00:00:00 day(2) to second(0)
interval
09/20/2025
11:43:19
09/20/2025 11:43:18
--> 동기화 완료 확인 후
--> 동기화 중지 후 오픈 시킨 후 다시 실시간
동기화 시킴
[ol7ora12s1]<SYS@ORA12R1>$ alter database recover managed standby database cancel;
[ol7ora12s1]<SYS@ORA12R1>$ alter database open read only;
[ol7ora12s1]<SYS@ORA12R1>$ ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING
CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
Elapsed: 00:00:06.04