RMAN Duplicate를 이용한 Standby 데이터베이스 구성

RMAN Duplicate 개요

RMAN Duplicate는 10g에서부터 지원된 기능으로, 기존에 스탠바이 구성시 따로 스탠바이 컨트롤파일을 만드는 등의 작업을 대신할 수 있도록 하고자 하는 목적을 갖고 있다. 11g 환경에서부터는 Active Session이라는 옵션을 통해 기 운영중인 데이터베이스의 복제가 가능하다.
이는 데이터가드 설정시, 스탠바이 데이터베이스를 구성하고자 할 때 주로 활용된다.

작업 수행

기본적인 환경의 설정은 다음과 같다.
Primary: 2-node RAC (SID: ORCL)
 IP주소:  rac1 노드 - 192.168.56.101 / rac2 노드 - 192.168.56.102
Standby: Single DB (SID: STBY)
 IP주소: stby 노드 - 192.168.56.120

먼저, Primary 데이터베이스를 Force Logging 모드로 변경한다. 이를 설정하는 것은 만약에 운영 단에 NOLOGGING 모드로 작업 수행시 이것이 스탠바이에 반영이 안되는 상황을 방지하고자 하는 것이다.

[oracle@rac1 ~]$ hostname rac1.kr.oracle.com [oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 8 09:42:02 2014 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> EXIT Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

그리고 Archive Log 모드로 변환한다. Archive Log 모드로의 변환은 MOUNT 상태에서만 가능하기 때문에 먼저 DB의 운영을 중단하므로, DB를 중단한 후, MOUNT 상태에서 Archive Log 모드로 변환하여 데이터베이스 재가동을 수행한다.

[oracle@rac1 ~]$ srvctl stop database -d orcl [oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 8 09:43:29 2014 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to an idle instance. SQL> STARTUP MOUNT; ORACLE instance started. Total System Global Area  626327552 bytes Fixed Size                  2255832 bytes Variable Size             297796648 bytes Database Buffers          322961408 bytes Redo Buffers                3313664 bytes Database mounted. SQL> ALTER DATABASE ARCHIVELOG; Database altered. SQL> ALTER DATABASE OPEN; Database altered. SQL> ARCHIVE LOG LIST; Database log mode              Archive Mode Automatic archival             Enabled Archive destination            USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     10 Next log sequence to archive   11 Current log sequence           11 SQL> EXIT Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production\ With the Partitioning, Real Application Clusters, Automatic Storage Management,  OLAP, Data Mining and Real Application Testing options [oracle@rac1 ~]$ srvctl start instance -d orcl -i orcl2

이제 STANDBY를 구성을 위해, 스탠바이 서버로 접속한 후 기본적인 데이터베이스 환경 구성을 위한 디렉토리 및 리스너를 생성하고 시작한다.

[oracle@rac1 ~]$ ssh stby oracle@stby's password: oracle Last login: Mon Apr  7 12:00:40 2014 from rac2.kr.oracle.com [oracle@stby ~]$ hostname stby.kr.oracle.com [oracle@stby ~]$ mkdir -p /u01/app/oracle/admin/stby [oracle@stby ~]$ mkdir -p /u01/app/oracle/admin/stby/adump [oracle@stby ~]$ mkdir -p /u01/app/oracle/admin/stby/dpdump [oracle@stby ~]$ mkdir -p /u01/app/oracle/admin/stby/pfile [oracle@stby ~]$ mkdir -p /u01/app/oradata/stby [oracle@stby ~]$ mkdir -p /u01/app/oradata/recovery_area/stby [oracle@stby ~]$ chmod -R 777 /u01/app/oradata/stby [oracle@stby ~]$ chmod -R 777 /u01/app/oradata/recovery_area/stby [oracle@stby ~]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-APR-2014 00:48:27 Copyright (c) 1991, 2013, Oracle.  All rights reserved. Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.4.0 - Production System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/stby/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stby.kr.oracle.com)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stby.kr.oracle.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date                08-APR-2014 00:48:27 Uptime                    0 days 0 hr. 0 min. 0 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File         /u01/app/oracle/diag/tnslsnr/stby/listener/alert/log.xml Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stby.kr.oracle.com)(PORT=1521)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))) Services Summary... Service "stby" has 1 instance(s).   Instance "stby", status UNKNOWN, has 1 handler(s) for this service... Service "stby_DGMGRL" has 1 instance(s).   Instance "stby", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully

<참고> RMAN으로 스탠바이 데이터베이스에 접속하기 위해서는 스탠바이 쪽의 listener.ora및 양쪽의 tnsnames.ora를 다음과 같이 설정해주어야 한다.

[oracle@stby ~]$ cat $ORACLE_HOME/network/admin/listener.ora SID_LIST_LISTENER=   (SID_LIST=     (SID_DESC=       (GLOBAL_DBNAME=stby)       (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)       (SID_NAME=stby))     (SID_DESC=      (GLOBAL_DBNAME=stby_DGMGRL)      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)      (SID_NAME=stby)     )   ) LISTENER=   (DESCRIPTION=     (ADDRESS_LIST=       (ADDRESS=(PROTOCOL=tcp)(HOST=stby.kr.oracle.com)(PORT=1521))       (ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))) ADR_BASE_LISTENER = /u01/app/oracle [oracle@stby ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora ORCL =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.kr.oracle.com)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = orcl)     )   ) ORCL1 =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.kr.oracle.com)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = orcl)     )   ) ORCL2 =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.kr.oracle.com)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = orcl)     )   ) STBY =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = stby.kr.oracle.com)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = stby)     )   )

RMAN 복제를 위해 스탠바이 데이터베이스를 NOMOUNT 모드로 시작한다. 이후에는 RMAN을 이용하여 작업을 수행하실 수 있다. (이미 PFILE은 생성했으며, 내용은 다음과 같다.)

[oracle@stby ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initstby.ora *.audit_trail='db' *.cluster_database=false *.compatible='11.2.0.4.0' *.db_block_size=8192 *.db_domain='' *.db_name='stby' *.db_recovery_file_dest='+DATA' *.db_recovery_file_dest_size=5368709120 *.dg_broker_start=TRUE *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' *.log_archive_format='%t_%s_%r.arc' *.log_archive_max_processes=4 *.open_cursors=300 *.pga_aggregate_target=109715200 *.processes=150 *.remote_login_passwordfile='exclusive' *.sga_target=429145600 [oracle@stby ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 8 00:49:02 2014 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to an idle instance. SQL> STARTUP NOMOUNT PFILE='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initstby.ora'; ORACLE instance started. Total System Global Area  430075904 bytes Fixed Size                  2253944 bytes Variable Size             184552328 bytes Database Buffers          239075328 bytes Redo Buffers                4194304 bytes SQL> EXIT Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

이제, RMAN을 이용하여 데이터베이스 복구를 진행한다. 다시 프라이머리 데이터베이스로 이동하여 RMAN을 다음과 같이 구동하면 된다.

[oracle@stby ~]$ ssh rac1 oracle@rac1's password: Last login: Tue Apr  8 09:49:46 2014 from stby.kr.oracle.com [oracle@rac1 ~]$ rman target sys/oracle@orcl1 auxiliary sys/oracle@stby Recovery Manager: Release 11.2.0.4.0 - Production on Fri Apr 4 06:49:26 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. connected to target database: ORCL (DBID=1370982891) connected to auxiliary database: STBY (not mounted) RMAN> run { duplicate target database for standby from active database spfile parameter_value_convert 'orcl','stby' set db_unique_name='stby' set cluster_database='false' set control_files='/u01/app/oradata/stby/control01.ctl', '/u01/app/oradata/stby/control02.ctl' set db_recovery_file_dest='/u01/app/oradata/stby' set db_create_file_dest='/u01/app/oradata/stby/' set db_file_name_convert='+DATA/orcl/datafile/','/u01/app/oradata/stby/', '+DATA/orcl/tempfile/', '/u01/app/oradata/stby/' set log_file_name_convert='+DATA/orcl/onlinelog/','/u01/app/oradata/stby/' set log_archive_max_processes='5' set standby_file_management='AUTO' nofilenamecheck ; } Starting Duplicate Db at 08-APR-14 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=20 device type=DISK contents of Memory Script: {    backup as copy reuse    targetfile  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl1' auxiliary format  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstby'   targetfile  '+DATA/orcl/spfileorcl.ora' auxiliary format  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestby.ora'   ;    sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestby.ora''"; } executing Memory Script Starting backup at 08-APR-14 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=66 instance=orcl1 device type=DISK Finished backup at 08-APR-14 sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestby.ora'' contents of Memory Script: {    sql clone "alter system set  audit_file_dest =  ''/u01/app/oracle/admin/stby/adump'' comment=  '''' scope=spfile";    sql clone "alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=stbyXDB)'' comment=  '''' scope=spfile";    sql clone "alter system set  db_unique_name =  ''stby'' comment=  '''' scope=spfile";    sql clone "alter system set  cluster_database =  false comment=  '''' scope=spfile";    sql clone "alter system set  control_files =  ''/u01/app/oradata/stby/control01.ctl'', ''/u01/app/oradata/stby/control02.ctl'' comment=  '''' scope=spfile";    sql clone "alter system set  db_recovery_file_dest =  ''/u01/app/oradata/stby'' comment=  '''' scope=spfile";    sql clone "alter system set  db_create_file_dest =  ''/u01/app/oradata/stby/'' comment=  '''' scope=spfile";    sql clone "alter system set  db_file_name_convert =  ''+DATA/orcl/datafile/'', ''/u01/app/oradata/stby/'', ''+DATA/orcl/tempfile/'', ''/u01/app/oradata/stby/                                                                    '' comment=  '''' scope=spfile";    sql clone "alter system set  log_file_name_convert =  ''+DATA/orcl/onlinelog/'', ''/u01/app/oradata/stby/'' comment=  '''' scope=spfile";    sql clone "alter system set  log_archive_max_processes =  5 comment=  '''' scope=spfile";    sql clone "alter system set  standby_file_management =  ''AUTO'' comment=  '''' scope=spfile";    shutdown clone immediate;    startup clone nomount; } executing Memory Script sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/stby/adump'' comment= '''' sc                                                                    ope=spfile sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=stbyXDB)'' comment= '''' scope=                                                                    spfile sql statement: alter system set  db_unique_name =  ''stby'' comment= '''' scope=spfile sql statement: alter system set  cluster_database =  false comment= '''' scope=spfile sql statement: alter system set  control_files =  ''/u01/app/oradata/stby/control01.ctl'', ''/u01/app/ora                                                                    data/stby/control02.ctl'' comment= '''' scope=spfile sql statement: alter system set  db_recovery_file_dest =  ''/u01/app/oradata/stby'' comment= '''' scope=s                                                                    pfile sql statement: alter system set  db_create_file_dest =  ''/u01/app/oradata/stby/'' comment= '''' scope=sp                                                                    file sql statement: alter system set  db_file_name_convert =  ''+DATA/orcl/datafile/'', ''/u01/app/oradata/stb                                                                    y/'', ''+DATA/orcl/tempfile/'', ''/u01/app/oradata/stby/'' comment= '''' scope=spfile sql statement: alter system set  log_file_name_convert =  ''+DATA/orcl/onlinelog/'', ''/u01/app/oradata/s                                                                    tby/'' comment= '''' scope=spfile sql statement: alter system set  log_archive_max_processes =  5 comment= '''' scope=spfile sql statement: alter system set  standby_file_management =  ''AUTO'' comment= '''' scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area     626327552 bytes Fixed Size                     2255832 bytes Variable Size                234882088 bytes Database Buffers             385875968 bytes Redo Buffers                   3313664 bytes contents of Memory Script: {    backup as copy current controlfile for standby auxiliary format  '/u01/app/oradata/stby/control01.ctl'                                                                    ;    restore clone controlfile to  '/u01/app/oradata/stby/control02.ctl' from  '/u01/app/oradata/stby/control01.ctl'; } executing Memory Script Starting backup at 08-APR-14 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl1.f tag=TAG20140408T095028 RECID=                                                                    2 STAMP=844336233 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 08-APR-14 Starting restore at 08-APR-14 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=19 device type=DISK channel ORA_AUX_DISK_1: copied control file copy Finished restore at 08-APR-14 contents of Memory Script: {    sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: {    set newname for tempfile  1 to  "/u01/app/oradata/stby/temp.266.843202209";    switch clone tempfile all;    set newname for datafile  1 to  "/u01/app/oradata/stby/system.256.843201987";    set newname for datafile  2 to  "/u01/app/oradata/stby/sysaux.257.843201987";    set newname for datafile  3 to  "/u01/app/oradata/stby/undotbs1.258.843201989";    set newname for datafile  4 to  "/u01/app/oradata/stby/users.259.843201989";    set newname for datafile  5 to  "/u01/app/oradata/stby/example.267.843202225";    set newname for datafile  6 to  "/u01/app/oradata/stby/undotbs2.268.843202725";    backup as copy reuse    datafile  1 auxiliary format  "/u01/app/oradata/stby/system.256.843201987"   datafile  2 auxiliary format  "/u01/app/oradata/stby/sysaux.257.843201987"   datafile  3 auxiliary format  "/u01/app/oradata/stby/undotbs1.258.843201989"   datafile  4 auxiliary format  "/u01/app/oradata/stby/users.259.843201989"   datafile  5 auxiliary format  "/u01/app/oradata/stby/example.267.843202225"   datafile  6 auxiliary format  "/u01/app/oradata/stby/undotbs2.268.843202725"   ;    sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oradata/stby/temp.266.843202209 in control file 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 backup at 08-APR-14 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=+DATA/orcl/datafile/system.256.843201987 output file name=/u01/app/oradata/stby/system.256.843201987 tag=TAG20140408T095048 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:55 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.843201987 output file name=/u01/app/oradata/stby/sysaux.257.843201987 tag=TAG20140408T095048 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=+DATA/orcl/datafile/example.267.843202225 output file name=/u01/app/oradata/stby/example.267.843202225 tag=TAG20140408T095048 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.258.843201989 output file name=/u01/app/oradata/stby/undotbs1.258.843201989 tag=TAG20140408T095048 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00006 name=+DATA/orcl/datafile/undotbs2.268.843202725 output file name=/u01/app/oradata/stby/undotbs2.268.843202725 tag=TAG20140408T095048 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=+DATA/orcl/datafile/users.259.843201989 output file name=/u01/app/oradata/stby/users.259.843201989 tag=TAG20140408T095048 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 08-APR-14 sql statement: alter system archive log current contents of Memory Script: {    switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=2 STAMP=844304122 file name=/u01/app/oradata/stby/system.256.843201987 datafile 2 switched to datafile copy input datafile copy RECID=3 STAMP=844304122 file name=/u01/app/oradata/stby/sysaux.257.843201987 datafile 3 switched to datafile copy input datafile copy RECID=4 STAMP=844304122 file name=/u01/app/oradata/stby/undotbs1.258.843201989 datafile 4 switched to datafile copy input datafile copy RECID=5 STAMP=844304122 file name=/u01/app/oradata/stby/users.259.843201989 datafile 5 switched to datafile copy input datafile copy RECID=6 STAMP=844304122 file name=/u01/app/oradata/stby/example.267.843202225 datafile 6 switched to datafile copy input datafile copy RECID=7 STAMP=844304122 file name=/u01/app/oradata/stby/undotbs2.268.843202725 Finished Duplicate Db at 08-APR-14 RMAN> EXIT Recovery Manager complete.

이렇게 RMAN Duplicate를 이용한 복제 디비 생성을 완료하였다.