Oracle在一臺機器上搭建dataguard

kakaxi9521發表於2017-10-19
這兩天找了臺機器搭建dataguard。
1. 環境準備。
作業系統
[oracle ~]$ lsb_release -a 
LSB Version:    :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
Distributor ID: RedHatEnterpriseServer
Description:    Red Hat Enterprise Linux Server release 6.8 (Santiago)
Release:        6.8
Codename:       Santiago
資料庫版本

SQL> select instance_name,version from v$instance;
INSTANCE_NAME    VERSION
---------------- -----------------
dgtest1          11.2.0.4.0
在機器上新建例項為dgtest1的資料庫。
2. 主庫上強記日誌。
alter database force logging;

3. 給standby 資料庫建立密碼檔案,由於是在一臺機器上,可以直接將主庫的密碼檔案copy 來用。
[oracle@bdev dbs]$ ls -l orapwdg*
-rw-r----- 1 oracle oinstall 1536 Oct 18 17:08 orapwdgtest1
-rw-r----- 1 oracle oinstall 1536 Oct 19 13:51 orapwdgtest2

4. 在主庫建立pfile。
create pfile='/home/oracle/pfiledgtest1.ora' from spfile;

5.編輯pfiledgtest.ora檔案。
[oracle ~]$ more pfiledgtest1.ora 
dgtest1.__db_cache_size=3338665984
dgtest1.__java_pool_size=67108864
dgtest1.__large_pool_size=83886080
dgtest1.__oracle_base='/ulic/app/oracle'#ORACLE_BASE set from environment
dgtest1.__pga_aggregate_target=2147483648
dgtest1.__sga_target=4294967296
dgtest1.__shared_io_pool_size=0
dgtest1.__shared_pool_size=771751936
dgtest1.__streams_pool_size=0
*.audit_file_dest='/ulic/app/oracle/admin/dgtest1/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/ulic/oradata/dgtest1/control01.ctl','/ulic/app/oracle/fast_recovery_area/dgtest1/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dgtest1'
*.db_recovery_file_dest='/ulic/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/ulic/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgtest1XDB)'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=2147483648
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=4294967296
*.undo_tablespace='UNDOTBS1'

#######需要新增的內容
DB_NAME=dgtest1
DB_UNIQUE_NAME=dgtest1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(dgtest1,dgtest2)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/ulic/archivelog/dgtest1/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=dgtest1'
LOG_ARCHIVE_DEST_2=
 'SERVICE=dgtest2 ARCH ASYNC NOAFFIRM
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=dgtest2'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
[oracle~]$ 

6. 修改完成後將主庫起來
SQL> startup pfile='/home/oracle/pfiledgtest1.ora';
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size                  2260088 bytes
Variable Size             939525000 bytes
Database Buffers         3321888768 bytes
Redo Buffers               12107776 bytes
Database mounted.
Database opened.
SQL>

7. 確定修改完pfile,資料庫還能正常起來後將資料庫給down下來。(這裡用來給資料庫做冷備)測試環境,所以能直接起停。

8. 建立standby資料庫,在oradata目錄下新建dgtest2目錄。
[oracle@oradata]$ pwd
/ulic/oradata
[oracle@bdev oradata]$ ls -l
total 8
drwxr-x--- 2 oracle oinstall 4096 Oct 18 17:07 dgtest1
drwxr-xr-x 2 oracle oinstall 4096 Oct 19 11:10 dgtest2

9. 將dgtest1目錄下的datafile和log複製到dgtest2目錄下。
[oracle@bdev dgtest2]$ ls -l
total 1561220
-rw-r----- 1 oracle oinstall  52429312 Oct 19 13:53 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Oct 19 13:53 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Oct 19 13:53 redo03.log
-rw-r----- 1 oracle oinstall 555753472 Oct 19 14:19 sysaux01.dbf
-rw-r----- 1 oracle oinstall 775954432 Oct 19 14:19 system01.dbf
-rw-r----- 1 oracle oinstall  30416896 Oct 19 11:05 temp01.dbf
-rw-r----- 1 oracle oinstall  73408512 Oct 19 14:19 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Oct 19 14:19 users01.dbf

10. 主庫開庫生成standby 控制檔案,並copy到dgtest2目錄下,並複製兩份到control01.ctl, control02.ctl。
alter database create standby controlfile as '/ulic/oradata/dgtest2/control.std';

[oracle@dgtest2]$ ls -l 
total 1561220
-rw-r----- 1 oracle oinstall   9748480 Oct 19 15:48 control01.ctl
-rw-r----- 1 oracle oinstall   9748480 Oct 19 15:48 control02.ctl
-rw-r----- 1 oracle asmadmin   9748480 Oct 19 11:08 control.std
-rw-r----- 1 oracle oinstall  52429312 Oct 19 13:53 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Oct 19 13:53 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Oct 19 13:53 redo03.log
-rw-r----- 1 oracle oinstall 555753472 Oct 19 14:19 sysaux01.dbf
-rw-r----- 1 oracle oinstall 775954432 Oct 19 14:19 system01.dbf
-rw-r----- 1 oracle oinstall  30416896 Oct 19 11:05 temp01.dbf
-rw-r----- 1 oracle oinstall  73408512 Oct 19 14:19 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Oct 19 14:19 users01.dbf

11. 複製一份pfiledgtest1.ora到pfiledgtest2.ora,並修改內容。
[oracle@~]$ more pfiledgtest2.ora
dgtest2.__db_cache_size=3338665984
dgtest2.__java_pool_size=67108864
dgtest2.__large_pool_size=83886080
dgtest2.__oracle_base='/ulic/app/oracle'#ORACLE_BASE set from environment
dgtest2.__pga_aggregate_target=2147483648
dgtest2.__sga_target=4294967296
dgtest2.__shared_io_pool_size=0
dgtest2.__shared_pool_size=771751936
dgtest2.__streams_pool_size=0
*.audit_file_dest='/ulic/app/oracle/admin/dgtest2/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/ulic/oradata/dgtest2/control01.ctl','/ulic/oradata/dgtest2/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_recovery_file_dest='/ulic/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/ulic/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgtest2XDB)'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=2147483648
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=4294967296
*.undo_tablespace='UNDOTBS1'


DB_NAME=dgtest1
DB_UNIQUE_NAME=dgtest2
LOG_ARCHIVE_CONFIG='DG_CONFIG=(dgtest1,dgtest2)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/ulic/archivelog/dgtest2/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=dgtest2'
LOG_ARCHIVE_DEST_STATE_1=ENABLE


FAL_SERVER=dgtest1
FAL_CLIENT=dgtest2
DB_FILE_NAME_CONVERT='/ulic/oradata/dgtest1','/ulic/oradata/dgtest2'
LOG_FILE_NAME_CONVERT='/ulic/oradata/dgtest1','/ulic/oradata/dgtest2' 
STANDBY_FILE_MANAGEMENT=AUTO

12. 配置監聽。

DGTEST1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bdev)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dgtest1)
    )
  )

DGTEST2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.18.1.123)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dgtest2)
    )
  )

13.啟動備庫到mount狀態。

SQL> startup mount pfile='/home/oracle/pfiledgtest2.ora';
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size                  2260088 bytes
Variable Size             939525000 bytes
Database Buffers         3321888768 bytes
Redo Buffers               12107776 bytes
Database mounted.

檢視備庫身份
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
檢視主庫身份
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
14. 從庫進行standby recover 開始應用日誌
alter database recover managed standby database disconnect from session;
15.切換日誌驗證是否能正常傳輸。






來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/21374452/viewspace-2146160/,如需轉載,請註明出處,否則將追究法律責任。

相關文章