RMAN을 이용한 CloneDB 생성

1. 백업할 디렉토리 생성 후 백업

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/rman/ctl_%F';

RMAN>  BACKUP DATABASE PLUS ARCHIVELOG FORMAT '/home/oracle/rman/database_%T_%U'; -- 풀백업

 

 

2. CloneDB가 사용할 디렉토리 생성 후 listener.ora, tnsnames.ora 파일 수정

 

$ mkdir /home/oracle/cloneDB

 

  listener.ora  

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

ADR_BASE_LISTENER = /app/oracle

DIAG_ADR_ENABLED_LISTENER = off

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON

 

  tnsnames.ora  

ORA19C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = HORA19C)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA19C)
    )
  )

LISTENER_ORA19C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = HORA19C)(PORT = 1521))


ORA19C2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = HORA19C)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA19C2)
    )
  )


LISTENER_ORA19C2 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = HORA19C)(PORT = 1521))

 

 

3. 파라미터 파일 복사 후 수정 (pfile 사용)

cp initORA19C.ora initORA19C2.ora

→ 파라미터 파일 내용 중 ORA19C -> ORA19C2로 변경 (:%s/ORA19C/ORA19C2/g)

→ 내용추가

db_file_name_convert=('/app/oracle/oradata/ORA19C','/home/oracle/cloneDB')
log_file_name_convert=('/app/oracle/oradata/ORA19C','/home/oracle/cloneDB')

위에서 생성한 디렉토리 이름을 넣어줘야함

→ control_files 위치는 아까 생성해놨던 디렉토리 (/home/oracle/cloneDB/) 로 지정

 

 

  initORA19C2.ora (pfile)  

ORA19C2.__data_transfer_cache_size=0
ORA19C2.__db_cache_size=1124073472
ORA19C2.__inmemory_ext_roarea=0
ORA19C2.__inmemory_ext_rwarea=0
ORA19C2.__java_pool_size=0
ORA19C2.__large_pool_size=16777216
ORA19C2.__oracle_base='/app/oracle'#ORACLE_BASE set from environment
ORA19C2.__pga_aggregate_target=536870912
ORA19C2.__sga_target=1610612736
ORA19C2.__shared_io_pool_size=83886080
ORA19C2.__shared_pool_size=369098752
ORA19C2.__streams_pool_size=0
ORA19C2.__unified_pga_pool_size=0
*.audit_file_dest='/app/oracle/admin/ORA19C2/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/app/oracle/cloneDB/control01.ctl','/app/oracle/cloneDB/control02.ctl'
*.db_block_size=8192
*.db_name='ORA19C2'
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA19CXDB)'
*.local_listener='LISTENER_ORA19C'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=512m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1536m
*.undo_retention=1200
*.undo_tablespace='UNDOTBS1'
db_file_name_convert=('/app/oracle/oradata/ORA19C','/home/oracle/cloneDB')
log_file_name_convert=('/app/oracle/oradata/ORA19C','/home/oracle/cloneDB')
~

 

 

5. passwd 생성

orapwd file=orapwORA19C2 password=oracle entries=5 format=12 force=y

 

 

6. home 디렉토리로 이동 후 .bash_profile_ORA19C2 생성

→ 마찬가지로 .bash_profile 내용 복사해서 만들면 되고, ORA19C, ORA19C2로 변경해주면 된다. 

 

.bash_profile_ORA19C2

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
export ORACLE_HOSTNAME=HORA19C
export ORACLE_UNQNAME=ORA19C2
export ORACLE_SID=ORA19C2

export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.3/db_1
export DATA_DIR=/app/oracle/oradata
export TNS_ADMIN=$ORACLE_HOME/network/admin


export PATH=$ORACLE_HOME/bin:$PATH:/bin:/usr/bin:/usr/sbin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export PS1='[$PWD]\\$ '

#export NLS_LANG=KOREAN_KOREA.AL32UTF8
#alias sqlplus='rlwrap sqlplus'
#alias ss='sqlplus / as sysdba'

 

 

 

7. sqlplus 접속 후 nomount 상태로 startup

→ 이때 memory 관련 에러가 난다면 initORA19C.ora(pfile)에 들어가서 *.memory_target 값을 줄여준다.

또 에러가 난다면 audit_file_dest를 새로 생성하지 않아서이다.

 

 

 

8. RMAN에서 복제 실행

 

rman nocatalog 

RMAN> connect target sys/oracle@ORA19C

RMAN> connect auxiliary /

RMAN> run {
allocate auxiliary channel c1 device type disk;
duplicate target database to ORA19C2;
}

 

 

  ※ 에러사항  

→ duplicate target database 에서 에러가 난다면 listener.ora나 tnsnames.ora가 제대로 설정되어 있지 않거나 구동(start)되어 있지 않을 확률이 높다.

 

 

 

상세진행과정

RMAN> connect target sys/oracle@ORA19C

connected to target database: ORA19C (DBID=1186485125)
using target database control file instead of recovery catalog

RMAN> connect auxiliary /

connected to auxiliary database: ORA19C2 (not mounted)

RMAN> run {
2> allocate auxiliary channel c1 device type disk;
3> duplicate target database to ORA19C2;
4> }

allocated channel: c1
channel c1: SID=134 device type=DISK

Starting Duplicate Db at 19-APR-23
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    1610609200 bytes

Fixed Size                     8897072 bytes
Variable Size                385875968 bytes
Database Buffers            1207959552 bytes
Redo Buffers                   7876608 bytes
allocated channel: c1
channel c1: SID=136 device type=DISK

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

sql statement: alter system set  db_name =  ''ORA19C'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''ORA19C2'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    1610609200 bytes

Fixed Size                     8897072 bytes
Variable Size                385875968 bytes
Database Buffers            1207959552 bytes
Redo Buffers                   7876608 bytes
allocated channel: c1
channel c1: SID=136 device type=DISK

Starting restore at 19-APR-23

channel c1: starting datafile backup set restore
channel c1: restoring control file
channel c1: reading from backup piece /home/oracle/rman/ctl_c-1186485125-20230419-06
channel c1: piece handle=/home/oracle/rman/ctl_c-1186485125-20230419-06 tag=TAG20230419T182337
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
output file name=/app/oracle/cloneDB/control01.ctl
output file name=/app/oracle/cloneDB/control02.ctl
Finished restore at 19-APR-23

database mounted

contents of Memory Script:
{
   set until scn  2807849;
   set newname for datafile  1 to
 "/home/oracle/cloneDB/system01.dbf";
   set newname for datafile  2 to
 "/home/oracle/cloneDB/user_ts/DAMIN_IDX_TS.dbf";
   set newname for datafile  3 to
 "/home/oracle/cloneDB/sysaux01.dbf";
   set newname for datafile  4 to
 "/home/oracle/cloneDB/undotbs01.dbf";
   set newname for datafile  5 to
 "/home/oracle/cloneDB/user_ts/DAMIN_DATA_TS.dbf";
   set newname for datafile  7 to
 "/home/oracle/cloneDB/users01.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

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 19-APR-23

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /home/oracle/cloneDB/system01.dbf
channel c1: restoring datafile 00002 to /home/oracle/cloneDB/user_ts/DAMIN_IDX_TS.dbf
channel c1: restoring datafile 00003 to /home/oracle/cloneDB/sysaux01.dbf
channel c1: restoring datafile 00004 to /home/oracle/cloneDB/undotbs01.dbf
channel c1: restoring datafile 00005 to /home/oracle/cloneDB/user_ts/DAMIN_DATA_TS.dbf
channel c1: restoring datafile 00007 to /home/oracle/cloneDB/users01.dbf
channel c1: reading from backup piece /app/oracle/rman/ORA19C_1o1q0op5_1_1_20230419.bk
channel c1: piece handle=/app/oracle/rman/ORA19C_1o1q0op5_1_1_20230419.bk tag=TAG20230419T182333
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:07
Finished restore at 19-APR-23

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=1134587908 file name=/home/oracle/cloneDB/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=1134587908 file name=/home/oracle/cloneDB/user_ts/DAMIN_IDX_TS.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=1134587908 file name=/home/oracle/cloneDB/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=1134587908 file name=/home/oracle/cloneDB/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=1134587908 file name=/home/oracle/cloneDB/user_ts/DAMIN_DATA_TS.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=12 STAMP=1134587908 file name=/home/oracle/cloneDB/users01.dbf

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

executing command: SET until clause

Starting recover at 19-APR-23

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /home/oracle/backup/ARCH/1_4_1134582938.dbf
archived log for thread 1 with sequence 5 is already on disk as file /home/oracle/backup/ARCH/1_5_1134582938.dbf
archived log file name=/home/oracle/backup/ARCH/1_4_1134582938.dbf thread=1 sequence=4
archived log file name=/home/oracle/backup/ARCH/1_5_1134582938.dbf thread=1 sequence=5
media recovery complete, elapsed time: 00:00:00
Finished recover at 19-APR-23
released channel: c1
Oracle instance started

Total System Global Area    1610609200 bytes

Fixed Size                     8897072 bytes
Variable Size                385875968 bytes
Database Buffers            1207959552 bytes
Redo Buffers                   7876608 bytes

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

sql statement: alter system set  db_name =  ''ORA19C2'' comment= ''Reset to original value by RMAN'' scope=spfile

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

Total System Global Area    1610609200 bytes

Fixed Size                     8897072 bytes
Variable Size                385875968 bytes
Database Buffers            1207959552 bytes
Redo Buffers                   7876608 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORA19C2" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP     1 ( '/home/oracle/cloneDB/redo01.log' ) SIZE 200 M  REUSE,
  GROUP     2 ( '/home/oracle/cloneDB/redo02.log' ) SIZE 200 M  REUSE,
  GROUP     3 ( '/home/oracle/cloneDB/redo03.log' ) SIZE 200 M  REUSE
 DATAFILE
  '/home/oracle/cloneDB/system01.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/home/oracle/cloneDB/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/home/oracle/cloneDB/user_ts/DAMIN_IDX_TS.dbf",
 "/home/oracle/cloneDB/sysaux01.dbf",
 "/home/oracle/cloneDB/undotbs01.dbf",
 "/home/oracle/cloneDB/user_ts/DAMIN_DATA_TS.dbf",
 "/home/oracle/cloneDB/users01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /home/oracle/cloneDB/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/home/oracle/cloneDB/user_ts/DAMIN_IDX_TS.dbf RECID=1 STAMP=1134587932
cataloged datafile copy
datafile copy file name=/home/oracle/cloneDB/sysaux01.dbf RECID=2 STAMP=1134587932
cataloged datafile copy
datafile copy file name=/home/oracle/cloneDB/undotbs01.dbf RECID=3 STAMP=1134587932
cataloged datafile copy
datafile copy file name=/home/oracle/cloneDB/user_ts/DAMIN_DATA_TS.dbf RECID=4 STAMP=1134587932
cataloged datafile copy
datafile copy file name=/home/oracle/cloneDB/users01.dbf RECID=5 STAMP=1134587932

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=1134587932 file name=/home/oracle/cloneDB/user_ts/DAMIN_IDX_TS.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1134587932 file name=/home/oracle/cloneDB/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1134587932 file name=/home/oracle/cloneDB/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=1134587932 file name=/home/oracle/cloneDB/user_ts/DAMIN_DATA_TS.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=5 STAMP=1134587932 file name=/home/oracle/cloneDB/users01.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database add supplemental log data

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

database opened
Cannot remove created server parameter file
Finished Duplicate Db at 19-APR-23

RMAN> exit


Recovery Manager complete.

 

 

 

 

9. sqlplus에 접속해서 정상적으로 복제된 부분 확인 가능