[ORACLE] RMAN Duplicate | Oracle 19c RAC 환경 Database 복제

테스트 환경

	HOST	DB_NAME	VERSION	STORAGE	ORACLE_HOME	GRID_HOME
SOURCE	rac1, rac2	rac	19.17.0.0.0(34416665)	ASM	/u01/app/oracle/product/19.0.0/dbhome_1	/u01/app/19c/grid
TARGET	racdp1, racdp2	racdp

RMAN Duplicate ?

RMAN Duplicate 기능을 사용하여 원본 데이베이스의 모든 데이터 또는 데이터 집합을 복제하는 것 

원본 데이터베이스와 완전히 독립적인 새로운 데이터베이스로 다른 서버 혹은 같은 서버에 복제본을 만드는 것임

 

- source host : 원본 데이터베이스를 호스트하는 컴퓨터

- source database instance : 원본 데이터베이스와 연결된 인스턴스

- target host (destination host) : 복제 데이터베이스를 호스트하는 컴퓨터 / source host server와 같은 곳에 할 수도 있고 다른 server에 할 수도 있음

- auxiliary instance : 복제 데이터베이스와 연결된 인스턴스 (보조 인스턴스)

 

Duplication은 원본 데이터베이스의 redo log file을 적용할 수 없기 때문에 Point-in-time 복구가 필요하다 (PITR).

 

Duplicate 과정

* 과정 시작 전 source,target의 database마다 node마다 network files, password file, parameter file 등 필요한 파일들을 백업해 놓는 것을 권장.

* 참고 : +FRA , +DATA 이름을 반대로 설정했습니다.... 참고바람 !

순번	작업내용	서버	노드	유저
1	archivelog mode enabled 확인	source	1	oracle

• source database의 archive log mode가 enabled 상태여야 함 (RMAN 사용하기 위해서는 필수)

$ sqlplus / as sysdba
SQL> archive log list ;
Database log mode	       Archive Mode
Automatic archival	       Enabled

# archive log mode disabled -> Enabled 변경
SQL> shut immediate
SQL> startup mount
SQL> alter database archivelog ;
SQL> alter database open ;

 

순번	작업내용	서버	노드	유저
2	새로운 pwd 파일 생성	source, target	1	oracle

• oracle version 12cR2 이상부터는 pwd파일 생성 시 비밀번호가 8자이상 및 특수문자가 필수이다. -> 그 외의 비밀번호를 사용하고 싶을 때는 12cR1버전으로 강제 설정을 적용하여 생성해준다.
• source, target의 비밀번호가 동일해야한다.

# force=y : 강제 설정
# format=12 : 12cR1 version 으로 비밀번호 생성

$ orapwd file=orapw$ORACLE_SID password=oracle force=y format=12

 

 

순번	작업내용	서버	노드	유저
3	tnsnames.ora file 수정	source, target	1	oracle

• source, target 서버의 1번 노드에 동일한 내용으로 작성해준다.

$ cd $ORACLE_HOME/network/admin

$ vi tnsnames.ora

---------------------------------------------------------------------------------------
RAC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE = yes)
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.101)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.102)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac)(UR = A)
    )
  )
RACDR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE = yes)
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.103)(PORT = 1721))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.104)(PORT = 1721))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdr)(UR = A)
    )
  )
---------------------------------------------------------------------------------------

 

순번	작업내용	서버	노드	유저
4	Listener 생성 및 시작	target	1	oracle

• 새로운 리스너를 생성해줬음

$ cd $ORACLE_HOME/network/admin
$ vi listener.ora

---------------------------------------------------------------------------------------
LISTENER_RACDR1 =
 (DESCRIPTION_LIST =
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.103)(PORT = 1721))
  )
 )

SID_LIST_LISTENER_RACDR1=
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = racdr)
      (SID_NAME = racdr1)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
    )
  )
---------------------------------------------------------------------------------------

# 시작
$ lsnrctl start LISTENER_RACDR1
$ lsnrctl status LISTENER_RACDR1

---------------------------------------------------------------------------------------

# TNSPING 확인

$ tnsping RAC

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 04-MAY-2023 07:36:37

Copyright (c) 1997, 2022, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE = yes) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.101)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.102)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = rac)(UR = A)))
OK (0 msec)

$ tnsping RACDR

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 04-MAY-2023 07:37:23

Copyright (c) 1997, 2022, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE = yes) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.103)(PORT = 1721)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.5.104)(PORT = 1721))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdr)(UR = A)))
OK (10 msec)

 

순번	작업내용	서버	노드	유저
5	target -> source로 원격 접속 체크	target	1	oracle

$ sqlplus sys/oracle@RAC as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 4 07:47:40 2023
Version 19.17.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0

SQL>  select instance_name, status from gv$instance ;

INSTANCE_NAME	 STATUS
---------------- ------------
rac1		 OPEN
rac2		 OPEN

 

순번	작업내용	서버	노드	유저
6	pfile 생성 및 수정	source	1	oracle

$ sqlplus / as sysdba
SQL> create pfile from spfile ;

# 수정
$ cp $ORACLE_HOME/dbs/initrac1.ora $ORACLE_HOME/dbs/initracdr1.ora
$ vi $ORACLE_HOME/dbs/initracdr1.ora

-----------------------------------------------------
# rac -> racdr로 전체 내용 변경 (*주의 : oracle이 oracdrle로 바뀜)
:%s/rac/racdr/g 
:%s/oracdrle/oracle/g -> 잘못 바뀐 것 변경
-----------------------------------------------------

# 그 외 바뀌어야 하는 것 및 확인 필요
*.db_name='racdr'
*.cluster_database=FALSE

*.remote_listener='racdr-scan:1721'
*.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.5.103)(PORT=1721))(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.5.104)(PORT=1721))))'

#*.sec_case_sensitive_logon=FALSE >> 삭제

원본

rac2.__data_transfer_cache_size=0
rac1.__data_transfer_cache_size=0
rac2.__db_cache_size=1761607680
rac1.__db_cache_size=1728053248
rac2.__inmemory_ext_roarea=0
rac1.__inmemory_ext_roarea=0
rac2.__inmemory_ext_rwarea=0
rac1.__inmemory_ext_rwarea=0
rac2.__java_pool_size=0
rac1.__java_pool_size=0
rac2.__large_pool_size=16777216
rac1.__large_pool_size=16777216
rac1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
rac2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
rac2.__pga_aggregate_target=838860800
rac1.__pga_aggregate_target=838860800
rac2.__sga_target=2516582400
rac1.__sga_target=2516582400
rac2.__shared_io_pool_size=134217728
rac1.__shared_io_pool_size=134217728
rac2.__shared_pool_size=587202560
rac1.__shared_pool_size=620756992
rac2.__streams_pool_size=0

rac1.__streams_pool_size=0
rac2.__unified_pga_pool_size=0
rac1.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/rac/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='19.0.0'
*.control_files='+FRA/RAC/CONTROLFILE/current.257.1135173641','+DATA/RAC/CONTROLFILE/current.256.1135173643'
*.db_block_size=8192
*.db_create_file_dest='+FRA'
*.db_name='rac'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=8451m
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racXDB)'
family:dw_helper.instance_mode='read-only'
rac2.instance_number=2
rac1.instance_number=1
rac1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.5.101)(PORT=1521))'
*.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.5.101)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.5.102)(PORT=1521))))'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=795m
*.processes=300
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.sga_target=2385m
rac2.thread=2
rac1.thread=1
*.undo_tablespace='UNDOTBS1'
rac1.undo_tablespace='UNDOTBS1'
rac2.undo_tablespace='UNDOTBS2'

수정본

racdr2.__data_transfer_cache_size=0
racdr1.__data_transfer_cache_size=0
racdr2.__db_cache_size=1761607680
racdr1.__db_cache_size=1728053248
racdr2.__inmemory_ext_roarea=0
racdr1.__inmemory_ext_roarea=0
racdr2.__inmemory_ext_rwarea=0
racdr1.__inmemory_ext_rwarea=0
racdr2.__java_pool_size=0
racdr1.__java_pool_size=0
racdr2.__large_pool_size=16777216
racdr1.__large_pool_size=16777216
racdr1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
racdr2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
racdr2.__pga_aggregate_target=838860800
racdr1.__pga_aggregate_target=838860800
racdr2.__sga_target=2516582400
racdr1.__sga_target=2516582400
racdr2.__shared_io_pool_size=134217728
racdr1.__shared_io_pool_size=134217728
racdr2.__shared_pool_size=587202560
racdr1.__shared_pool_size=620756992
racdr2.__streams_pool_size=0
racdr1.__streams_pool_size=0
racdr2.__unified_pga_pool_size=0
racdr1.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/racdr/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='19.0.0'
*.control_files='+FRA/RAC/CONTROLFILE/current.257.1135173641','+DATA/RAC/CONTROLFILE/current.256.1135173643'
*.db_block_size=8192
*.db_create_file_dest='+FRA'
*.db_name='racdr'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=8451m

*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdrXDB)'
family:dw_helper.instance_mode='read-only'
racdr2.instance_number=2
racdr1.instance_number=1
*.remote_listener='racdr-scan:1721'
*.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.5.103)(PORT=1721))(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.5.104)(PORT=1721))))'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=795m
*.processes=300
*.remote_login_passwordfile='exclusive'
#*.sec_case_sensitive_logon=FALSE
*.sga_target=2385m
racdr2.thread=2
racdr1.thread=1
*.undo_tablespace='UNDOTBS1'
racdr1.undo_tablespace='UNDOTBS1'
racdr2.undo_tablespace='UNDOTBS2'

 

순번	작업내용	서버	노드	유저
7	수정한 pfile source -> target 의 각 노드로 이동	source	1	oracle

$ scp -pr $ORACLE_HOME/dbs/initrac1.ora oracle@10.0.5.103:$ORACLE_HOME/dbs/initracdr1.ora
$ scp -pr $ORACLE_HOME/dbs/initrac1.ora oracle@10.0.5.104:$ORACLE_HOME/dbs/initracdr2.ora

 

순번	작업내용	서버	노드	유저
8	target DB nomount startup	target	1	oracle

$ export ORACLE_SID=racdr1
$ sqlplus / as sysdba

SQL> startup nomount pfile='/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initracdr1.ora' ;

ORACLE instance started.

Total System Global Area 2516581464 bytes
Fixed Size		    8899672 bytes
Variable Size		  603979776 bytes
Database Buffers	 1895825408 bytes
Redo Buffers		    7876608 bytes

 

순번	작업내용	서버	노드	유저
9	target RMAN 접속	target	1	oracle

• source -> target 으로 duplicate을 실행할 것이기 때문에 아래와 같은 target, auxiliary를 설정하여 접속

$ rman target sys/oracle@RAC auxiliary sys/oracle@RACDR

Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 4 08:07:56 2023
Version 19.17.0.0.0

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

connected to target database: RAC (DBID=2723155654)
connected to auxiliary database: RACDR (not mounted)

RMAN>

 

순번	작업내용	서버	노드	유저
10	rman duplicate - cmd ver	target	1	oracle

• 기본 duplicate 명령어
  duplicate target database to [target DB NAME] from active database;

run
{
allocate channel src1 type disk;
allocate channel src2 type disk;
allocate channel src3 type disk;
allocate channel src4 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
allocate auxiliary channel aux3 type disk;
allocate auxiliary channel aux4 type disk;
duplicate target database to racdr from active database USING BACKUPSET ;
}

내 서버에 맞는 할당 channel 갯수 구하기 : rman channel (parallelism) 기준은 CPU Core 갯수에 따라 결정됨

select a.value * b.value "Recommended_ch" from v$parameter a, v$parameter b where a.name = 'cpu_count' and b.name = 'parallel_threads_per_cpu';

 

 

순번	작업내용	서버	노드	유저
11	* 참조 : rman duplicate - shell script ver	target	1	oracle

mkdir -p /media/script/log
chown oracle:oinstall /media/script
chmod 755 /media/script

vi /media/script/rman_clone.cmd

run
{
allocate channel src1 type disk;
allocate channel src2 type disk;
allocate channel src3 type disk;
allocate channel src4 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
allocate auxiliary channel aux3 type disk;
allocate auxiliary channel aux4 type disk;
duplicate target database to racdr from active database USING BACKUPSET ;
}


vi /media/script/rman_clone_RACDR.sh

#!/bin/ksh
rman target sys/oracle@RAC auxiliary sys/oracle@RACDR msglog /media/script/log/rman_clone_RACDR.log cmdfile=/media/script/rman_clone.cmd

nohup sh rman_clone_RACDR.sh &


# nohup monitoring
tail -50f /media/script/log/rman_clone_RACDR.log

로그가 필요하신갑쇼

[BEGIN] 2023-05-03 10:14:53
[racdr1|oracle:/media/script/log]> cat rman_clone_RACDR.log

Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 3 10:09:32 2023
Version 19.17.0.0.0

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

connected to target database: RAC (DBID=2723155654)
connected to auxiliary database: RACDR (not mounted)

RMAN> run
2> {
3> allocate channel src1 type disk;
4> allocate channel src2 type disk;
5> allocate channel src3 type disk;
6> allocate channel src4 type disk;
7> allocate auxiliary channel aux1 type disk;
8> allocate auxiliary channel aux2 type disk;
9> allocate auxiliary channel aux3 type disk;
10> allocate auxiliary channel aux4 type disk;
11> duplicate target database to racdr from active database USING BACKUPSET ;
12> }
13> 
using target database control file instead of recovery catalog
allocated channel: src1
channel src1: SID=49 instance=rac2 device type=DISK

allocated channel: src2
channel src2: SID=290 instance=rac2 device type=DISK

allocated channel: src3
channel src3: SID=57 instance=rac1 device type=DISK

allocated channel: src4
channel src4: SID=66 instance=rac2 device type=DISK

allocated channel: aux1
channel aux1: SID=264 device type=DISK

allocated channel: aux2
channel aux2: SID=263 device type=DISK

allocated channel: aux3
channel aux3: SID=27 device type=DISK

allocated channel: aux4
channel aux4: SID=262 device type=DISK

Starting Duplicate Db at 2023/05/03 10:09:39
current log archived

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    2516581464 bytes

Fixed Size                     8899672 bytes
Variable Size                603979776 bytes
Database Buffers            1895825408 bytes
Redo Buffers                   7876608 bytes
allocated channel: aux1
channel aux1: SID=23 device type=DISK
allocated channel: aux2
channel aux2: SID=261 device type=DISK
allocated channel: aux3
channel aux3: SID=24 device type=DISK
allocated channel: aux4
channel aux4: SID=25 device type=DISK
duplicating Online logs to Oracle Managed File (OMF) location
duplicating Datafiles to Oracle Managed File (OMF) location

contents of Memory Script:
{
   sql clone "alter system set  control_files = 
 comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name = 
 ''RAC'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''racdr'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone from service  'RAC' primary controlfile;
   alter clone database mount;
}
executing Memory Script

'+DATA/RACDR/CONTROLFILE/current.274.1135851015'' comment= ''Set by RMAN'' scope=spfile

e

ope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    2516581464 bytes

Fixed Size                     8899672 bytes
Variable Size                603979776 bytes
Database Buffers            1895825408 bytes
Redo Buffers                   7876608 bytes
allocated channel: aux1
channel aux1: SID=23 device type=DISK
allocated channel: aux2
channel aux2: SID=261 device type=DISK
allocated channel: aux3
channel aux3: SID=24 device type=DISK
allocated channel: aux4
channel aux4: SID=25 device type=DISK

Starting restore at 2023/05/03 10:10:52

channel aux1: starting datafile backup set restore
channel aux1: using network backup set from service RAC
channel aux1: restoring control file
channel aux1: restore complete, elapsed time: 00:00:02
output file name=+FRA/RACDR/CONTROLFILE/current.259.1135851015
output file name=+DATA/RACDR/CONTROLFILE/current.274.1135851015
Finished restore at 2023/05/03 10:10:56

database mounted

contents of Memory Script:
{
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   restore
   from  nonsparse   from service 
 'RAC'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2023/05/03 10:11:01

channel aux1: starting datafile backup set restore
channel aux1: using network backup set from service RAC
channel aux1: specifying datafile(s) to restore from backup set
channel aux1: restoring datafile 00001 to +FRA
channel aux2: starting datafile backup set restore
channel aux2: using network backup set from service RAC
channel aux2: specifying datafile(s) to restore from backup set
channel aux2: restoring datafile 00002 to +FRA
channel aux3: starting datafile backup set restore
channel aux3: using network backup set from service RAC
channel aux3: specifying datafile(s) to restore from backup set
channel aux3: restoring datafile 00003 to +FRA
channel aux4: starting datafile backup set restore
channel aux4: using network backup set from service RAC
channel aux4: specifying datafile(s) to restore from backup set
channel aux4: restoring datafile 00004 to +FRA
channel aux3: restore complete, elapsed time: 00:00:04
channel aux3: starting datafile backup set restore
channel aux3: using network backup set from service RAC
channel aux3: specifying datafile(s) to restore from backup set
channel aux3: restoring datafile 00005 to +FRA
channel aux4: restore complete, elapsed time: 00:00:03
channel aux4: starting datafile backup set restore
channel aux4: using network backup set from service RAC
channel aux4: specifying datafile(s) to restore from backup set
channel aux4: restoring datafile 00006 to +FRA
channel aux3: restore complete, elapsed time: 00:00:00
channel aux4: restore complete, elapsed time: 00:00:03
channel aux1: restore complete, elapsed time: 00:00:12
channel aux2: restore complete, elapsed time: 00:00:11
Finished restore at 2023/05/03 10:11:13

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   restore clone force from service  'RAC' 
           archivelog from scn  1398860;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 2023/05/03 10:11:19

channel aux1: starting archived log restore to default destination
channel aux1: using network backup set from service RAC
channel aux1: restoring archived log
archived log thread=1 sequence=38
channel aux2: starting archived log restore to default destination
channel aux2: using network backup set from service RAC
channel aux2: restoring archived log
archived log thread=1 sequence=39
channel aux3: starting archived log restore to default destination
channel aux3: using network backup set from service RAC
channel aux3: restoring archived log
archived log thread=2 sequence=9
channel aux4: starting archived log restore to default destination
channel aux4: using network backup set from service RAC
channel aux4: restoring archived log
archived log thread=2 sequence=10
channel aux1: restore complete, elapsed time: 00:00:01
channel aux1: starting archived log restore to default destination
channel aux1: using network backup set from service RAC
channel aux1: restoring archived log
archived log thread=2 sequence=11
channel aux2: restore complete, elapsed time: 00:00:01
channel aux3: restore complete, elapsed time: 00:00:01
channel aux4: restore complete, elapsed time: 00:00:01
channel aux1: restore complete, elapsed time: 00:00:01
Finished restore at 2023/05/03 10:11:22

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=1135851082 file name=+FRA/RACDR/DATAFILE/system.258.1135851063
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=1135851082 file name=+FRA/RACDR/DATAFILE/sysaux.265.1135851063
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=1135851082 file name=+FRA/RACDR/DATAFILE/undotbs1.264.1135851063
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=1135851082 file name=+FRA/RACDR/DATAFILE/undotbs2.273.1135851063
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=1135851082 file name=+FRA/RACDR/DATAFILE/users.274.1135851067
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=1135851082 file name=+FRA/RACDR/DATAFILE/ts_sample.275.1135851067

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

executing command: SET until clause

Starting recover at 2023/05/03 10:11:22

starting media recovery

/thread_1_seq_38.271.1135851081
/thread_1_seq_39.273.1135851081
/thread_2_seq_10.270.1135851081
/thread_2_seq_11.263.1135851081
ce=38
ce=10
ce=11
ce=39
media recovery complete, elapsed time: 00:00:01
Finished recover at 2023/05/03 10:11:25

contents of Memory Script:
{
   delete clone force archivelog all;
}
executing Memory Script

deleted archived log
135851080
deleted archived log
135851080
Deleted 1 objects

deleted archived log
135851081
Deleted 1 objects

deleted archived log
35851081
Deleted 1 objects

deleted archived log
135851081
Deleted 2 objects

released channel: src1
released channel: src2
released channel: src3
released channel: src4
released channel: aux1
released channel: aux2
released channel: aux3
released channel: aux4
Oracle instance started

Total System Global Area    2516581464 bytes

Fixed Size                     8899672 bytes
Variable Size                603979776 bytes
Database Buffers            1895825408 bytes
Redo Buffers                   7876608 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''RACDR'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
}
executing Memory Script

e=spfile

sql statement: alter system reset  db_unique_name scope=spfile
Oracle instance started

Total System Global Area    2516581464 bytes

Fixed Size                     8899672 bytes
Variable Size                603979776 bytes
Database Buffers            1895825408 bytes
Redo Buffers                   7876608 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "RACDR" RESETLOGS ARCHIVELOG 
  MAXLOGFILES    192
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES    32
  MAXLOGHISTORY      292
 LOGFILE
  GROUP     1  SIZE 200 M ,
  GROUP     2  SIZE 200 M 
 DATAFILE
  '+FRA/RACDR/DATAFILE/system.258.1135851063'
 CHARACTER SET AL32UTF8

sql statement: ALTER DATABASE ADD LOGFILE 
  
  INSTANCE 'i2' 
  GROUP     3  SIZE 200 M ,
  GROUP     4  SIZE 200 M 

contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "+FRA/RACDR/DATAFILE/sysaux.265.1135851063", 
 "+FRA/RACDR/DATAFILE/undotbs1.264.1135851063", 
 "+FRA/RACDR/DATAFILE/undotbs2.273.1135851063", 
 "+FRA/RACDR/DATAFILE/users.274.1135851067", 
 "+FRA/RACDR/DATAFILE/ts_sample.275.1135851067";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +FRA in control file

cataloged datafile copy
datafile copy file name=+FRA/RACDR/DATAFILE/sysaux.265.1135851063 RECID=1 STAMP=1135851116
cataloged datafile copy
datafile copy file name=+FRA/RACDR/DATAFILE/undotbs1.264.1135851063 RECID=2 STAMP=1135851116
cataloged datafile copy
datafile copy file name=+FRA/RACDR/DATAFILE/undotbs2.273.1135851063 RECID=3 STAMP=1135851116
cataloged datafile copy
datafile copy file name=+FRA/RACDR/DATAFILE/users.274.1135851067 RECID=4 STAMP=1135851116
cataloged datafile copy
datafile copy file name=+FRA/RACDR/DATAFILE/ts_sample.275.1135851067 RECID=5 STAMP=1135851116

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=1135851116 file name=+FRA/RACDR/DATAFILE/sysaux.265.1135851063
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1135851116 file name=+FRA/RACDR/DATAFILE/undotbs1.264.1135851063
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1135851116 file name=+FRA/RACDR/DATAFILE/undotbs2.273.1135851063
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=1135851116 file name=+FRA/RACDR/DATAFILE/users.274.1135851067
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=1135851116 file name=+FRA/RACDR/DATAFILE/ts_sample.275.1135851067

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Cannot remove created server parameter file
Finished Duplicate Db at 2023/05/03 10:12:02

Recovery Manager complete.

[END] 2023-05-03 10:15:03

 

 

이제 듀플은 잘 끝났을 거고

사후작업입니다 !

 

순번	작업내용	서버	노드	유저
12	cluster_database parameter 원복	target	1	oracle

$ sqlplus / as sysdba

SQL> alter system set cluster_database=TRUE scope=spfile sid='*';

 

순번	작업내용	서버	노드	유저
13	target spfile을 asm disk에 생성	target	1,2	oracle

------> 1번 노드만 
# 현재 PFILE = initracdr1.ora
# initTest는 ORACLE_SID가 들어가지 않게 생성해야함 (spfile을 만들기 위한 명령어임)
SQL> create pfile='initTest.ora' from spfile ;

SQL> create spfile='+DATA/RACDR/PARAMETERFILE/spfileracdr.ora' from pfile;
SQL> exit

------> 1,2번 노드 모두 진행
# 아까 수정했던 pfile에 내용 추가 및 변경
$ cd $ORACLE_HOME/dbs
vi initracdr1.ora
vi initracdr2.ora

spfile='+FRA/RACDR/PARAMETERFILE/spfileracdr.ora' #추가
*.cluster_database=true #변경

 

순번	작업내용	서버	노드	유저
14	database , instance 등록	target	1	oracle

$ srvctl add database -db racdr -oraclehome /u01/app/oracle/product/19.0.0/dbhome_1
$ srvctl add instance -d racdr -i racdr1 -node racdr1
$ srvctl add instance -d racdr -i racdr2 -node racdr2
$ srvctl modify database -d racdr -spfile +DATA/RACDR/PARAMETERFILE/spfileracdr.ora

$ srvctl config database -d racdr
Database unique name: racdr
Database name: 
Oracle home: /u01/app/oracle/product/19.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/RACDR/PARAMETERFILE/spfileracdr.ora
Password file: 
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: DATA,FRA
Mount point paths: 
Services: 
Type: RAC
Start concurrency: 
Stop concurrency: 
OSDBA group: dba
OSOPER group: oper
Database instances: racdr1,racdr2
Configured nodes: racdr1,racdr2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services: 
Database is administrator managed



# database로 등록됨 확인

$ crsctl stat res -t | grep -A3 ora.racdr.db

ora.racdr.db
      1        OFFLINE OFFLINE                               STABLE
      2        OFFLINE OFFLINE                               STABLE

 

순번	작업내용	서버	노드	유저
15	현재 컨트롤파일 확인	target	1	grid

$ asmcmd -p
ASMCMD [+] > cd +FRA/RACDR/CONTROLFILE
ASMCMD [+FRA/RACDR/CONTROLFILE] > ls -l
Type         Redund  Striped  Time             Sys  Name
CONTROLFILE  UNPROT  FINE     MAY 03 14:00:00  Y    Current.258.1135868027
CONTROLFILE  UNPROT  FINE     MAY 04 06:00:00  Y    Current.271.1135868027

 

순번	작업내용	서버	노드	유저
16	재기동	target	1	oracle

 

$ sqlplus / as sysdba

SQL> shut immediate
SQL> startup nomount

# 아까 찾은 control_files로 변경
SQL> alter system set control_files='+FRA/RACDR/CONTROLFILE/Current.271.1135868027' scope=spfile sid='*';
SQL> shut immediate
SQL> startup mount
SQL> exit

$ srvctl start database -d racdr
$ crsctl stat res -t

ora.racdr.db
      1        ONLINE  ONLINE       racdr1                   Open,HOME=/u01/app/o
                                                             racle/product/19.0.0
                                                             /dbhome_1,STABLE
      2        ONLINE  ONLINE       racdr2                   Open,HOME=/u01/app/o
                                                             racle/product/19.0.0
                                                             /dbhome_1,STABLE

 

*** trouble shooting ***

srvctl start database -d racdr 시 PRCR-1079 CRS-5017 ORA-01102 CRS-2674 CRS-2632

오류가 계속 났었음 -> 해당 오류는 파라미터를 잘못 설정하면 발생한다 함

이것저것 찾아보고 시도해본 결과 cluster_database=TRUE로 안바꿔줘서 발생한 오류였음

alter system으로 바꾼 후에도 해당 파라미터는 memory에서는 바뀌지 않고 spfile에만 변경된 상태이기 때문에 다시 재시작해줘야함

혹시나 뜬다면 더보기 확인하세요!

PRCR-1079 : Failed to start resource ora.racdr.db
CRS-5017: The resource action "ora.racdr.db start" encountered the following error: 
ORA-01102: cannot mount database in EXCLUSIVE mode
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/racdr2/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.racdr.db' on 'racdr2' failed
CRS-2632: There are no more servers to try to place resource 'ora.racdr.db' on that would satisfy its placement policy

해당 오류 날 시 
SQL> show parameter cluster_database
확인 후 false인 경우
alter system set cluster_database=TRUE scope=spfile sid='*'; 
shut immediate
srvctl start database -d racdr

 

 

순번	작업내용	서버	노드	유저
17	target에서 table 조회로 확인	target	1	oracle

# source에 있던 table이 잘 생성되었는지 확인 - 본인의 데이터로 검증하시면 됩니다..

SQL> select table_name from dba_tables where owner='SAMPLE';

TABLE_NAME
--------------------------------------------------------------------------------
COUNTRIES
A_REGIONS
A_LOCATIONS
A_WAREHOUSER
A_EMPLOYEES
A_PRODUCT_CATEGORIES
A_PRODUCTS
A_CUSTOMERS
A_CONTACTS
A_ORDERS
A_INVENTORIES

TABLE_NAME
--------------------------------------------------------------------------------
A_ORDER_ITEMS

12 rows selected.

# rows 수 검증 비교
SQL> col TABLE_NAME for a20
SQL> set line 200 pages 1000
SQL> SELECT TABLE_NAME, NUM_ROWS FROM DBA_TABLES WHERE OWNER = 'SAMPLE';

## source
TABLE_NAME	       NUM_ROWS
-------------------- ----------
COUNTRIES		     25
A_REGIONS		      4
A_LOCATIONS		     23
A_WAREHOUSER		      9
A_EMPLOYEES		    117
A_PRODUCT_CATEGORIES	      5
A_PRODUCTS		    288
A_CUSTOMERS		    324
A_CONTACTS		    319
A_ORDERS		    105
A_INVENTORIES		   1112
A_ORDER_ITEMS		    665

12 rows selected.

## target
TABLE_NAME	       NUM_ROWS
-------------------- ----------
COUNTRIES		     25
A_REGIONS		      4
A_LOCATIONS		     23
A_WAREHOUSER		      9
A_EMPLOYEES		    117
A_PRODUCT_CATEGORIES	      5
A_PRODUCTS		    288
A_CUSTOMERS		    324
A_CONTACTS		    319
A_ORDERS		    105
A_INVENTORIES		   1112
A_ORDER_ITEMS		    665

12 rows selected.