본문 바로가기
Oracle/admin

Oracle Dataguard Install

소마후니 2016. 6. 16. 15:21

Active Server

OS : oracle linux 6.5 64bit

DB : oracle 11.2.0.4

IP : 172.16.1.218

SID : ora11g

DB_UNIQUE_NAME : ora11g

DB_NAME : ora11g

Character set : AL32UTF8

ORACLE_HOME : /u01/app/oracle/product/11.2.0/db_1


Standby Server (DB 생성시 지정하는 값들은 신경 안써도 됨 -> DB 생성 없이 복구를 통해  DB 생성하기때문)

OS : oracle linux 6.5 64bit

DB : oracle 11.2.0.4

IP : 172.16.1.219

SID : ora11gs

DB_UNIQUE_NAME : ora11gs

DB_NAME : ora11g <--Duplicate 이기때문에 DB Name 은 같지만 DB_Unique_name은 틀림

Character set : AL32UTF8

ORACLE_HOME : /u01/app/oracle/product/11.2.0/db_1



1. Active Server

OS 설치 후 DB까지 설치한다. (나중에 Standby Server는 Oracle Engine만 설치한다)


LISTENER.ORA 파일 수정

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ora11g)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = ora11g)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle



TNSNAMES.ORA 파일 수정

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORA11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.218)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11g)
    )
  )


ORA11GS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.219)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11gs)
    )
  )


LISTENER_ORA11G =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.218)(PORT = 1521))




initora11g.ora 파일 수정


*.__db_cache_size=377487360
*.__java_pool_size=4194304
*.__large_pool_size=8388608
*.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.__pga_aggregate_target=301989888
*.__sga_target=536870912
*.__shared_io_pool_size=0
*.__shared_pool_size=138412032
*.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ora11g/adump'
*.audit_trail='none'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/ora11g/control01.ctl','/u01/app/oracle/fast_recovery_area/ora11g/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ora11g'
*.db_unique_name='ora11g'
*.dg_broker_start=true
*.fal_client='ora11g'
*.fal_server='ora11gs'
*.log_archive_config='DG_CONFIG=(ora11g,ora11gs)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/ora11g/archive valid_for=(all_logfiles,all_roles) db_unique_name=ora11g'
*.log_archive_dest_2='SERVICE=ora11gs NOAFFIRM ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=ora11gs'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='arc%t_%r_%s.arc'
*.standby_file_management='auto'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'
*.local_listener='LISTENER_ORA11G'
*.open_cursors=300
*.pga_aggregate_target=301989888
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
#*.sga_target=536870912
*.undo_tablespace='UNDOTBS1'





DB Mount 후 Archive log mode 로 변경

SQL> alter database archivelog;


DB Open.

SQL> alter database open;


Nologging 작업이 일어나지 않도록 Logging 변경

SQL> alter database force logging;






2.Stanby Server


Oracle Engine 까지 설치함. (아래의 각각 파일들은 해당 디폴트 위치에 있어야함)



listener.ora 파일 수정

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ora11gs)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = ora11gs)
    )
  )


ADR_BASE_LISTENER = /u01/app/oracle



tnsnames.ora 파일 수정

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORA11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.218)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11g)
    )
  )


ORA11GS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.219)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11gs)
    )
  )


LISTENER_ORA11GS =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.219)(PORT = 1521))




Active Server의 Initora11g.ora 파일을 가져와서 이름변경 후 내용 수정


initora11gs.ora(이름변경) 파일 수정


*.__db_cache_size=377487360
*.__java_pool_size=4194304
*.__large_pool_size=8388608
*.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.__pga_aggregate_target=301989888
*.__sga_target=536870912
*.__shared_io_pool_size=0
*.__shared_pool_size=138412032
*.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ora11gs/adump'
*.audit_trail='none'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/ora11gs/control01.ctl','/u01/app/oracle/fast_recovery_area/ora11gs/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ora11g'
*.db_unique_name='ora11gs'
*.dg_broker_start=true
*.fal_client='ora11gs'
*.fal_server='ora11g'
*.db_file_name_convert='ora11g','ora11gs'
*.log_file_name_convert='ora11g','ora11gs'

*.log_archive_config='DG_CONFIG=(ora11g,ora11gs)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/ora11gs/archive valid_for=(all_logfiles,all_roles) db_unique_name=ora11gs'
*.log_archive_dest_2='SERVICE=ora11g ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=ora11g'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='arc%t_%r_%s.arc'
*.standby_file_management='auto'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gsXDB)'
*.local_listener='LISTENER_ORA11GS'
*.open_cursors=300
*.pga_aggregate_target=301989888
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
#*.sga_target=536870912
*.undo_tablespace='UNDOTBS1'



파라미터를 $ORACLE_HOME/dbs 아래에 위치한후 DB를 nomount로 올리면 디렉토리 or 파일이 없다고 에러 or 경고가 나타난다.
그때마다 해당 경로에 디렉토리를 만들어주면 된다.

DB를 nomount 상태로 기동

SQL> startup nomount



3. tnsping or 직접 tns를 통한 sqlplus 접속을 통해 네트워크 연결이 잘 되는지 확인한다.


4. active server

rman 백업 폴더 생성(/u01/app/oracle/rman_backup)

Rman Backup 실행


$ rman target /


RMAN>configure controlfile autobackup format for device type disk to '/u01/app/oracle/rman_backup/%F';

RMAN>backup as compressed backupset database format '/u01/app/oracle/rman_backup/rman_%U.bkp' plus archivelog format '/u01/app/oracle/rman_backup/rman_%t%s.bkp' ;



Active Server DB에서 Controlfile 백업 (Stanby Controlfile)

SQL> alter database create standby controlfile as '/u01/app/oracle/rman_backup/standby.ctl';


생성한 controlfile은 standby server로 복사한 뒤 initora11gs에 명시한 controlfile 위치와 이름으로 만들어준다.



rman으로 백업받은 백업본도 standby 서버에 같은 위치를 만들어서 복사한다.

active

/u01/app/oracle/rman_backup/* 

==>

standby

/u01/app/oracle/rman_backup/



active server 에서 Rman을 이용해 standby DB로 접속한다.


$ rman target / auxiliary sys/oracle@ora11gs;


백업 된 파일을 이용해서 standby DB를 복원한다.


RMAN> duplicate target database for standby;



완료가 되면 Standby DB는 Mount 상태가 된다.

Standby DB에 접속해서 확인해보자.


Standby DB에서 standby log 파일을 등록해준다. (Swithover 시 Active DB가 Standby DB가 되므로 미리 Active DB에도 똑같이 생성해준다)

SQL> alter database add standby logfile '/u01/app/oracle/oradata/ora11gs/standbylog01.log' size 52428800;
SQL> alter database add standby logfile '/u01/app/oracle/oradata/ora11gs/standbylog02.log' size 52428800;
SQL> alter database add standby logfile '/u01/app/oracle/oradata/ora11gs/standbylog03.log' size 52428800;


구축이 완료 되었다.


Active DB에서 log switch 를 발생하면, 양쪽 DB의 alertlog에서 동시에 같은 시퀀스의 로그가 떨어지는걸 확인할 수 있다.



SQL> alter system archive log current;



11g real-time query capability of physical standby

11g 부터는 stnadby db open 상태에서도 redo apply 가 가능해졌다.

즉 Standby DB도 open 상태로 두고 실시간 redo 적용이 가능해졌다는 이야기다.


mount 상태의 Standby DB에서


SQL> alter database recover managed standby database disconnect from session;
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database using current logfile disconnect;     <-- Real-time Redo Apply 적용


온라인 상태에서 Redo를 적용하게 되면 standby DB는 Read only with apply 상태로 운영된다.



저작자표시 비영리 변경금지

'Oracle > admin' 카테고리의 다른 글

SQL LOADER  (0) 2016.06.24
Dataguard Switchover  (0) 2016.06.16
패스워드 verify 설정 및 해제  (0) 2016.06.14
Oracle TDE  (0) 2016.06.14
batch 파일중 SQL 부하 찾기  (0) 2016.06.13

'Oracle/admin' Related Articles

티스토리툴바