Oracle ADG實施步驟

主庫
IP地址:192.168.1.100
作業系統版本：rhel6 64bit
資料庫版本：11.2.0.4 64bit
資料庫sid名：orcl
資料庫名：orcl
資料庫db_unique_name:orcl


備庫1  物理備庫 （只安裝oracle資料庫軟體，無需建庫）
IP地址:192.168.1.101
作業系統版本：rhel6 64bit
資料庫版本：11.2.0.4 64bit
資料庫sid名：orcl
資料庫名：orcl
資料庫db_unique_name:orcldg


1.主庫開啟歸檔
alter system set LOG_ARCHIVE_DEST_1=‘LOCATION=/u01/app/oracle/arch’ scope=spfile;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

2. 主庫開啟LOGGING
ALTER DATABASE FORCE LOGGING

3. 傳輸主庫orapworcl 到備庫
SCP  orapwdorcl  XXXX:$ORACLE_HOME/dbs

4.修改主庫listener.ora靜態註冊 (主備一模一樣)
vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
   (SID_DESC =
     (SID_NAME = orcl)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
     (GLOBAL_DBNAME= orcl)
    )
)
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    )
  )


ADR_BASE_LISTENER = /u01/app/oracle


ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent


5.修改主庫和備庫的tnsname.ora (主備一模一樣)
vi tnsnames.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.99.129)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.99.128)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

6.修改主庫的spfile
alter system set DB_UNIQUE_NAME=orcl scope=spfile;

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg)’;

alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=orcl’ scope=spfile;

alter system set LOG_ARCHIVE_DEST_2='SERVICE=orcldg LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=orcldg’ scope=both;

alter system set FAL_SERVER=orcldg scope=both;

alter system set FAL_CLIENT=orcl scope=both;

alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;

可選
*.db_file_name_convert='/u01/app/oradata/dg/','/u01/app/oradata/dg/'
*.log_file_name_convert='/u01/app/oradata/dg/','/u01/app/oradata/dg/'

7.建立主庫的備份
rman target /
run {
backup database format '/u01/app/oracle/arch/db_%t_%s_%U.bak' tag='db_full01';
sql "alter system archive log current";
backup archivelog all format '/u01/app/oracle/arch/arch_%t_%s_%U.bak' tag='arch01';
}
8.主庫上建立備庫的controlfile
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/arch/control01.ctl';

9.複製檔案到備端
scp * 192.168.1.101:/u01/app/oracle/arch

10.主庫建立standby log
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 4 ('+DATA/orcl/onlinelog/SREDO4.LOG') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 5 ('+DATA/orcl/onlinelog/SREDO5.LOG') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 6 ('+DATA/orcl/onlinelog/SREDO6.LOG') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 7 ('+DATA/orcl/onlinelog/SREDO7.LOG') SIZE 50M;


10. 建立主庫spfile
shutdown immediate;
startup;
create pfile from spfile;

11.建立備庫的spfile
vi initorcl.ora
[oracle@dg1 ~]$ cat /home/oracle/initorcl.ora ?
orcl.__db_cache_size=176160768
orcl.__java_pool_size=4194304
orcl.__large_pool_size=71303168
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=209715200
orcl.__sga_target=369098752
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=100663296
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA/orcl/controlfile/current.260.874065153'
*.db_4k_cache_size=8388608
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='example.com'
*.db_name='orcl'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4294967296
*.db_unique_name='ORCLDG'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_goldengate_replication=TRUE
*.fal_client='ORCLDG'
*.fal_server='ORCL'
*.log_archive_config='DG_CONFIG=(orcl,orcldg)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=orcldg'
*.log_archive_dest_2='SERVICE=orcl LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=orcl'
*.memory_target=576716800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
（紅色標註的為需要修改的）

12. 傳輸spfile到備庫
scp initorcl.ora 192.168.1.62:/home/oracle/


13.建立必須的資料夾
mkdir -p /u01/app/oracle/admin/orcl/adump

14. 透過pfile和control啟動備庫到mount狀態
sqlplus / as sysdba
startup mount;


15.透過RMAN恢復備庫
rman target /
RMAN> restore database from tag='db_full01';
RMAN> restore archivelog all;
RMAN> recover database;


16.建立備庫的standby log，備庫操作
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 4 ('+DATA/orcldg/onlinelog/SREDO4.LOG') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 5 ('+DATA/orcldg/onlinelog/SREDO5.LOG') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 6 ('+DATA/orcldg/onlinelog/SREDO6.LOG') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 7 ('+DATA/orcldg/onlinelog/SREDO7.LOG') SIZE 50M;


17.開啟備庫的實時日誌應用，備庫操作
alter database recover managed Standby database disconnect from session no timeout

18.啟用ADG，備庫操作
startup；
alter database recover managed standby database using current logfile disconnect from session;