linux下oracle 11g dg環境搭建

一、主庫操作

確認主庫是否打開歸檔、force logging

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE FORCE LOGGING;

SQL> SELECT NAME,LOG_MODE,FORCE_LOGGING FROM v$DATABASE;

修改主庫參數

SQL> create pfile='/u01/pfile.ora' from spfile;

SQL> alter system set db_unique_name=uniquepdg scope=both;

SQL> alter system set log_archive_config='dg_config=(uniquepdg,uniquesdg)' scope=both;

SQL> alter system set log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=uniquepdg' scope=spfile;

SQL> alter system set log_archive_dest_2='service=tns_sdg  async valid_for=(online_logfile,primary_role) db_unique_name=uniquesdg' scope=both;

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;  //默認即是exclusive

SQL> alter system set log_archive_max_processes=10 scope=both;

主庫上有關standby角色的參數

SQL> alter system set fal_server=tns_sdg scope=both;

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

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

SQL> alter system set standby_file_management=auto scope=both;

從庫傳送pfile參數

scp /u01/pfile2.ora 192.168.12.31:$ORACLE_HOME/dbs/

從主庫拷貝密碼文件

$ cd $ORACLE_HOME/dbs

$ scp orapworcl 192.168.12.23:$ORACLE_HOME/dbs/orapworcl

二、從庫操作

創建相應目錄

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

$ mkdir -p /u01/oradata/orcl

$ mkdir -p /u01/app/oracle/fast_recovery_area

$ mkdir -p /u01/arch

$ mkdir -p /u01/backup

備庫以spfile啟動

SQL> startup nomount  pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfile2.ora'

SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfile2.ora'

SQL> shutdown immediate;

SQL> startup nomount;

主庫進行全備

rman target /

run {

allocate channel d0 type disk;

allocate channel d1 type disk;

backup format '/u01/backup02/full_t%t_s%s_p%p' database;

sql 'alter system archive log current';

backup format '/u01/backup02/arc_t%t_s%s_p%p' archivelog all;

release channel d0;

release channel d1;

}

創建備用控制文件

backup current controlfile for standby format '/u01/backup02/control01.ctl';

拷貝備份文件及備用控制文件到備庫

$ cd /u01/backup

$ scp * 192.168.12.23:/u01/backup02

從庫恢復控制文件

$ rman target /

RMAN> restore standby controlfile from '/u01/backup02/control01.ctl';

RMAN> alter database mount;

從備庫還原數據文件

RMAN> restore database;

RMAN> recover database;----------------------------恢復完成后數據庫處于mount狀態

Connected.

SQL> select  open_mode  from  v$database;

OPEN_MODE

----------------------------------------

MOUNTED

主庫：

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/oradata/orcl/sredo04.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/oradata/orcl/sredo05.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/oradata/orcl/sredo06.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/oradata/orcl/sredo07.log' size 50M;

備庫：

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/oradata2/orcl/sredo04.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/oradata2/orcl/sredo05.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/oradata2/orcl/sredo06.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/oradata2/orcl/sredo07.log' size 50M;

三、配置主備庫監聽及tns文件

主庫監聽文件

$ more /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = uniquepdg)

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

      (SID_NAME = orclpdg)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

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

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

  )

備庫監聽文件

$ more /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = uniquesdg)

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

      (SID_NAME = orclsdg)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

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

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

  )

主備庫tns文件

$ more /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

TNS_PDG =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = uniquepdg)

    )

  )

TNS_SDG =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = uniquesdg)

    )

  )

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

備庫打開只讀模式

SQL> alter database open;         

//10g無法啟動到read only模式,只能啟動到mount模式

備庫啟動實時應用

SQL> alter database recover managed standby database using current logfile disconnect from session;

三、日常運維管理

DG環境的啟動與關閉 

DG環境的關閉 

檢查DG環境主備庫的日志使用情況 

操作位置：主庫&備庫 

SQL> archive log list; 

主庫與備庫當前使用的日志編號相同

停主庫的監聽程序 

lsnrctl stop 

停備庫的監聽程序 

lsnrctl stop 

關閉主數據庫 

SQL> shutdown immediate;

查看備庫的開啟模式 

SQL> select open_mode from v$database;  

如果發現當前數據庫是read only with apply模式，則需要執行下面命令關閉歸檔日志應用程序，

如果發現是read only模式則直接關閉數據庫即可。正常情況下備庫應該時刻處于應用歸檔日志的模式。 

關閉備數據庫的歸檔應用程序 

SQL> alter database recover managed standby database cancel; 

關閉備數據庫 

SQL> shutdown immediate; 

這樣，整個Data Guard環境就算是完整的關閉掉了...

DG環境的啟動 

啟動DG環境的主庫 

sqlplus / as sysdba 

SQL> startup; 

SQL> select status from v$instance; 

啟動主庫的監聽程序 

lsnrctl start 

啟動DG環境的備庫到mount或open狀態 

sqlplus / as sysdba 

SQL> startup; 

SQL> startup mount; 

啟動備庫的監聽程序 

lsnrctl start 

主庫切換歸檔日志 

操作命令： 

SQL> alter system archive log current; 

查看備庫是否有新應用過來的日志 

SQL> select sequence#,applied from v$archived_log; 

備庫上開啟歸檔日志應用進程 

SQL> alter database recover managed standby database disconnect from session; 

主庫與備庫驗證當前redo log 

操作位置：主庫&備庫 

SQL> archive log list; 

如果此時發現主庫與備庫當前使用的redo日志的編號一致則說明重啟的DG環境一切正常。 

這樣，這個Data Guard環境就算是去正常的啟動了... 

日常運維操作

 檢查主備庫是否存在GAP

主庫檢查current sequence#

SQL> select thread#,sequence#,status from v$log;

   THREAD#  SEQUENCE# STATUS

---------- ---------- ----------------

         1        847 INACTIVE

         1        848 INACTIVE

         1        849 CURRENT

主庫檢查LNS進程正在寫的sequence#

SQL> select process,sequence#,status from v$managed_standby;

PROCESS    SEQUENCE# STATUS

--------- ---------- ------------

ARCH              847 CLOSING

ARCH              848 CLOSING

LNS               849 WRITING

備庫檢查正在應用的sequence#

SQL> SELECT PROCESS,THREAD#,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;

PROCESS      THREAD#  SEQUENCE# STATUS

--------- ---------- ---------- ------------

ARCH               1        848 CLOSING

ARCH               1        847 CLOSING

MRP0               1        849 APPLYING_LOG

RFS                0          0 IDLE

RFS                1        849 IDLE

檢查是否存在GAP

SQL> SELECT THREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;---由此可判斷主備庫無GAP，可進行正常switchover切換

set linesize 300

col DATABASE_ROLE  for 20

col DB_UNIQUE_NAME for a30 

col INSTANCE   for a30  

col OPEN_MODE  for a30

col PROTECTION_MODE  for a30

col PROTECTION_LEVEL   for a30

col SWITCHOVER_STATUS   for a30

SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE,PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

配置文件內容說明

主庫

orcl.__db_cache_size=264241152

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=314572800

orcl.__sga_target=465567744

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=117440512

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='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain='segment2'

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

*.db_name='orcl'

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

*.db_recovery_file_dest_size=4385144832

*.db_unique_name='UNIQUEPDG'

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

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

*.fal_server='TNS_SDG'

*.log_archive_config='dg_config=(uniquepdg,uniquesdg)'

*.log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=uniquepdg'

*.log_archive_dest_2='service=tns_sdg  async valid_for=(online_logfile,primary_role) db_unique_name=uniquesdg'

*.log_archive_max_processes=10

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

*.memory_target=780140544

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

從庫配置文件

orcl.__db_cache_size=327155712

orcl.__java_pool_size=4194304

orcl.__large_pool_size=8388608

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

orcl.__pga_aggregate_target=314572800

orcl.__sga_target=465567744

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=117440512

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='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain='segment2'

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

*.db_name='orcl'

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

*.db_recovery_file_dest_size=4385144832

*.db_unique_name='UNIQUESDG'

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

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

*.fal_server='TNS_PDG'

*.log_archive_config='dg_config=(uniquepdg,uniquesdg)'

*.log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=uniquesdg'

*.log_archive_dest_2='service=tns_pdg  async valid_for=(online_logfile,primary_role) db_unique_name=uniquepdg'

*.log_archive_max_processes=10

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

*.memory_target=780140544

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

主庫監聽

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = uniquepdg)

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

      (SID_NAME = orcl)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

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

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

從庫監聽

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = uniquesdg)

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

      (SID_NAME = orcl)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

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

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle