oracle11g dg 配置（圖文版）

有點懶，大家多見諒圖片的位置就不一一添加了，工作比較忙，只給大家復制粘貼了下我的文檔。

第三章 主庫配置

3.1 創(chuàng)建目錄并修改名稱

//在主庫創(chuàng)建所需的文件夾

mkdir /u01/app/oracle/arch

//修改名稱

sqlplus / assysdba

show parametername

alter system set db_unique_name=orclscope=spfile;

?。。?！如果默認是上圖內(nèi)容，則可不必執(zhí)行下面2段sql語句?。。。?/span>

alter system set service_names=orclscope=spfile;

alter system set instance_name=orclscope=spfile;

3.2 配置日志歸檔模式

shutdownimmediate;

startup mount

//設置歸檔日志目錄

alter system set log_archive_dest_1='location=/u01/app/oracle/arch';

//驗證日志目錄

archive log list

//開啟歸檔模式

Alter databasearchivelog;

//開啟強制記錄日志模式

alter databaseforce logging;

驗證

selectFORCE_LOGGING from v$database;

3.3 添加 standby redolog日志文件

alter database add standby logfile group 11 '/u01/app/oracle/oradata/orcl/std11.log'size 60M;

alter database add standby logfile group 12 '/u01/app/oracle/oradata/orcl/std12.log'size 60M;

alter database add standby logfile group 13 '/u01/app/oracle/oradata/orcl/std13.log'size 60M;

alter database add standby logfile group 14 '/u01/app/oracle/oradata/orcl/std14.log'size 60M;

//驗證，加上系統(tǒng)默認的三條共7條

Select member,type from v$logfile;

3.4 開啟閃回數(shù)據(jù)庫

//開啟閃回數(shù)據(jù)庫（作用：當發(fā)生故障轉(zhuǎn)移時，這個功能非常有用，它能讓你將老的主庫閃回到故障前，然后將其轉(zhuǎn)換為備庫。如果沒有啟用閃回功能，你就必須重建備庫，意味著要再復制一次數(shù)據(jù)文件。除了這個好處，閃回還能在某些情況下讓你避免從備份恢復數(shù)據(jù)）

//查看閃回數(shù)據(jù)庫狀態(tài)

show parameter db_recovery_file_dest

//更改路徑和大小

可以修改位置：sql>alter system set db_recovery_file_dest='新路徑';
       更改大?。?/span>sql>alter system set db_recovery_file_dest_size=400G;

//查看是否啟用，默認是不開啟的

select flashback_on from v$database;

alter database flashback on;

//驗證

select flashback_on from v$database;

3.5 DG配置

// Data Guard 配置里的另外一個庫的名字

alter system setlog_archive_config='DG_CONFIG=(orcl,orclstd)';

//配置歸檔日志位置（很重要）

alter system setlog_archive_dest_1='LOCATION=/u01/app/oracle/archVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';

//配置重做日志到備份庫（很重要）

alter system set log_archive_dest_2='SERVICE=orclstdASYNC LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstd';

//設置日志格式

alter system setlog_archive_format='arch_%r_%t_%s.arc' scope=spfile;

//設置FAL_SERVER（作用：這個參數(shù)指定當日志傳輸出現(xiàn)問題時，備庫到哪里去找缺少的歸檔日志）

alter system set fal_server=orclstd;

alter system set fal_client=orcl;

//配置數(shù)據(jù)文件和日志文件目錄

show parameter convert

alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'scope=spfile;

alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'scope=spfile;

shutdown immediate

startup mount

//驗證

show parameter convert

3.6 設置自動管理

//查看主庫是否自動管理文件（作用：主庫添加或刪除數(shù)據(jù)文件時，備庫也跟著添加刪除數(shù)據(jù)文件）

show parameter standby

alter system set standby_file_management=auto;

//驗證

show parameter standby

3.7 修改fal參數(shù)

alter system set fal_client=orcl;

alter system set fal_server=orclstd;

//驗證

show parameter fal;

3.8 配置密碼文件

//由于默認的密碼不知道，所以可以刪除或備份一份密碼文件

cd /u01/app/oracle/product/11.2.0/db_1/dbs/

mv orapworcl orapworcl.bak

創(chuàng)建密碼文件

orapwd file=orapworcl password=jinhetech force=y

3.9 配置監(jiān)聽

cd/u01/app/oracle/product/11.2.0/db_1/network/admin/

在監(jiān)聽文件里添加：

vim listener.ora

===========================================================

SID_LIST_LISTENER =

 (SID_LIST =

   (SID_DESC =

     (GLOBAL_DBNAME = orcl)

     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

     (SID_NAME = orcl)

    )

 )

LISTENER =

 (DESCRIPTION_LIST =

    (DESCRIPTION=

      (ADDRESS =(PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS =(PROTOCOL = TCP)(HOST = compute02)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

===========================================================

重啟監(jiān)聽，重啟數(shù)據(jù)庫.

驗證：

lsnrctl status

//配置遠程監(jiān)聽，添加一下內(nèi)容

vim tnsnames.ora

===========================================================

ORCL =

  (DESCRIPTION =

    (ADDRESS =(PROTOCOL = TCP)(HOST = compute02)(PORT = 1521))

   (CONNECT_DATA =

      (SERVER =DEDICATED)

     (SERVICE_NAME = orcl)

    )

  )

ORCLSTD =

 (DESCRIPTION =

   (ADDRESS = (PROTOCOL = TCP)(HOST = controller)(PORT = 1521))

   (CONNECT_DATA =

     (SERVER = DEDICATED)

     (SERVICE_NAME = orclstd)

    )

  )

===========================================================

驗證

tnsping compute02

3.10 設置參數(shù)文件

//生成修改主庫的pfile用于備庫

create pfile='/tmp/init.ora' from spfile;

//生成自己的pfile配置文件

create pfile from spfile;

//分別修改主備庫配置

//修改下面兩個文件配置，手動添加紅色字段

vim /u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora

vim /tmp/init.ora

===========================================================

orcl.__db_cache_size=7784628224

orcl.__java_pool_size=268435456

orcl.__large_pool_size=268435456

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE setfrom environment

orcl.__pga_aggregate_target=11005853696

orcl.__sga_target=16106127360

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=7516192768

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'

*.db_name='orcl'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=4070572032

*.db_unique_name='ORCL'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.fal_client='ORCL'

*.fal_server='ORCLSTD'

*.log_archive_config='DG_CONFIG=(orcl,orclstd)'

*.log_archive_dest_1='LOCATION=/u01/app/oracle/archVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'

*.log_archive_dest_2='SERVICE=orclstd ASYNC LGWRVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstd'

*.log_archive_format='arch_%r_%t_%s.arc'

*.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'

*.memory_target=26902265856

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

===========================================================

3.11 創(chuàng)建控制文件

alter database create standby controlfile as '/tmp/controlstb.ctl';

3.12 傳輸文件

！！?。。?！按順序執(zhí)行?。。。?！

//傳輸密碼文件到備庫

cd /u01/app/oracle/product/11.2.0/db_1/dbs/

scp orapworcl oracle@172.16.0.1:/u01/app/oracle/product/11.2.0/db_1/dbs/

//拷貝監(jiān)聽文件到備庫

cd /u01/app/oracle/product/11.2.0/db_1/network/admin/

scp listener.ora tnsnames.ora oracle@172.16.0.1:/u01/app/oracle/product/11.2.0/db_1/network/admin/

//拷貝參數(shù)文件到備庫

scp /tmp/init.ora oracle@172.16.0.1:/u01/app/oracle/product/11.2.0/db_1/dbs/

//拷貝數(shù)據(jù)文件到備庫

scp /u01/app/oracle/oradata/orcl/* oracle@172.16.0.1:/u01/app/oracle/oradata/orcl/

//拷貝控制文件到備庫

scp /tmp/controlstb.ctloracle@172.16.0.1:/u01/app/oracle/oradata/orcl/control01.ctl

scp /tmp/controlstb.ctl oracle@172.16.0.1:/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

第四章 備庫配置

4.1 創(chuàng)建所需的文件夾

//在備庫創(chuàng)建所需的文件夾

mkdir -p/u01/app/oracle/oradata/orcl/

mkdir -p/u01/app/oracle/arch/

mkdir -p /u01/app/oracle/flash_recovery_area/orcl

mkdir -p /u01/app/oracle/admin/orcl/adump

mkdir -p /u01/app/oracle/admin/orcl/dpdump

chown -R oracle:oinstall/u01/app

chmod 6751/u01/app

4.2 修改參數(shù)文件

cd/u01/app/oracle/product/11.2.0/db_1/dbs/

vim init.ora

//修改init.ora為如下內(nèi)容：

===========================================================

*.db_unique_name='orclstd'

fal_server='orcl'

fal_client='orclstd'

log_archive_dest_1='location=/u01/app/oracle/archVALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclstd'

log_archive_dest_2='service=orcl valid_for=(online_logfiles,primary_role)db_unique_name=orcl'

========================================================

4.3 配置監(jiān)聽

cd /u01/app/oracle/product/11.2.0/db_1/network/admin/

vim listener.ora

========================================================

SID_LIST_LISTENER=

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orclstd)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

      (SID_NAME = orclstd)

    )

 )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = controller)(PORT = 1521))

    )

  )

===========================================================

//啟動監(jiān)聽

lsnrctl start

//驗證

tnsping orcl

tnsping orclstd

第五章 日常維護

5.1 啟動方式（先啟動備庫在啟動主庫）

//備庫操作

shut immediate

startup nomountpfile='/u01/app/oracle/product/11.2.0/db_1/dbs/init.ora';

alter databasemount standby database;

alter databaseopen read only;

select open_modefrom v$database;

alter databaserecover managed standby database using current logfile disconnect from session;

select open_modefrom v$database;

archive log list;

//生成自己的pfile配置文件

create pfile from spfile;

//主庫操作

shut immediate

startup

//主庫最終監(jiān)聽狀態(tài)

//備庫最終監(jiān)聽狀態(tài)